Sometimes you get some dynamic SQL handed to you which is 2 pages long with all kind of parameters. Of course you yourself would never write such a monstrosity, but how can you verify that all parameters in the code have been declared? Let's say you have the following piece of dynamic SQL

That will execute just fine. What if you forgot the DECLARE @Type nchar(3) = ''P'' part?

Here is the error.
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@Type".

What if there was something that could help you find these kind of problems? There is, in SQL Server 2012 we now have the sp_describe_undeclared_parameters stored procedure

Here is how Books On Line describes the sp_describe_undeclared_parameters stored procedure

Returns a result set that contains metadata about undeclared parameters in a Transact-SQL batch. Considers each parameter that is used in the @tsql batch, but not declared in @params. A result set is returned that contains one row for each such parameter, with the deduced type information for that parameter. The procedure returns an empty result set if the @tsql input batch has no parameters except those declared in @params.

Here is how you can easily test the code. Change exec( @DynamicSql ) to exec sp_describe_undeclared_parameters @DynamicSql

Here is what you see

There are more columns in the output, feel free to run this yourself to see all of them. Basically it will give you type, precision, length parameter name and more

How many times have you done something like the following

Running that will give you the following error

Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@name".

Here is how to quickly test that as well, take the code from above and grab everything except for this part ,@id = 1,@name = 'sysobjects'

Basically, you now have this

Now change sp_executesql to sp_describe_undeclared_parameters

Run that

Here is what you get

parameter_ordinal name	suggested_system_type_id suggested_system_type_name	
1	          @name	231	                 nvarchar(128)	

Again there are more columns so feel free to run this yourself. So far I showed you examples with just one parameter, the stored procedure will return all undeclared parameters

Here is what it looks like with 4 undeclared parameters

Here is the output

parameter_ordinal name suggested_system_type_id	suggested_system_type_name suggested_max_length
1	         @Type	239	                nchar(3)	           6
2	         @Name	231	                nvarchar(35)	          70
3	        @number	56	                int	                   4
4	          @high	56	                int	                   4

As you can see the sp_describe_undeclared_parameters stored procedure can come in handy to check if dynamic SQL has undeclared parameters