Grappling with sp_describe_undeclared_parameters(). The Hows, Whys and Wherefores.

sp_describe_undeclared_parameters() promises a great deal, but delivers less. It was added mostly for executing application queries via sp_prepare using a TDS link, but has several quirks and restrictions that rather spoils its more general use.

It is designed to provide you with all the parameters of a batch. You might use it when a SQL statement could contain parameters and it has to be somehow executed by eliciting the intended values. One can imagine it being used for a rather clunky user-interface.

It would be nice to have a utility that will do the @Params parameter for the sp_executeSQL system procedure when you are doing a lot of routine work but there are problems. It introduce restrictions, both intentionally and accidentally to such an extent that its use is liable to be restrictive

sp_describe_undeclared_parameters returns an error if …

  • You use an input or output variable twice in a query
  • You try to use table variables
  • You use the wrong case for the parameter of a procedure called within the batch
  • the statement references temporary tables.
  • Any expression has two arguments without data types
  • The query creates a permanent table that is then used
  • It cannot successfully parse the input @tsql
  • If you supply a known parameter that is not syntactically valid, or you declare any parameter more than one time.
  • the input batch declares a local variable of the same name as a parameter that you supply in @params.

If you try to use an input or output variable twice in a query, it won’t work.

This gives you an error

This is an odd restriction when one considers that it works fine with sp_executeSQL

… which gives …

You can get around the problem of not being allowed to use input values by assigning the input value to a local variable in the batch.

You can cope with output variables too.

… producing …

..and yes, it identified the @OutputReturn as an output parameter too.

Fussy? It all works fine with sp_ExecuteSQL without any need for this.

What if you can live with these restrictions? Here is a stored procedure that can help with a complicated batch with lots of embedded parameters that you need to execute with sp_ExecuteSQL. Just don’t think of using a parameter twice, or any of the other fussy rules! This will work with SQL Server 2017 upwards but it can easily be adapted for earlier versions by changing the string_agg() function for the less-intuitive XML trick. 
If you are using this in SSMS, I’d choose to operate the query pane in TEXT more rather than grid and increase the text length to something reasonable (query->Results to->).

If, for example, we execute this

We get this as a result…

All we need to do now is to paste it from the results into the code pane, change the value of the parameter to something reasonable and bang the button

And it all then works fine


We have quite a useful utility procedure here for dealing with the chore of writing the code for executing batches with several parameters using sp_ExecuteSQL. It could be just my own clumsiness, but I never quite get it right first time without it. I have to warn you though that there are a few restrictions that can be rather irritating unless you know the fixes.