{"id":86431,"date":"2020-02-19T18:10:13","date_gmt":"2020-02-19T18:10:13","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=86431"},"modified":"2020-02-19T18:10:13","modified_gmt":"2020-02-19T18:10:13","slug":"grappling-with-sp_describe_undeclared_parameters-the-hows-whys-and-wherefores","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/grappling-with-sp_describe_undeclared_parameters-the-hows-whys-and-wherefores\/","title":{"rendered":"Grappling with sp_describe_undeclared_parameters(). The Hows, Whys and Wherefores."},"content":{"rendered":"<p><strong>sp_describe_undeclared_parameters()<\/strong> promises a great deal, but delivers less. It was added mostly for executing application queries via <strong>sp_prepare <\/strong>using a TDS link, but has several quirks and restrictions that rather spoils its more general use.<\/p>\n<p>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.<\/p>\n<p>It would be nice to have a utility that will do the @Params parameter for the<strong> sp_executeSQL<\/strong> 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<\/p>\n<p>sp_describe_undeclared_parameters\u00a0returns an error if \u2026<\/p>\n<ul>\n<li>You use an input or output variable twice in a query<\/li>\n<li>You try to use table variables<\/li>\n<li>You use the wrong case for the parameter of a procedure called within the batch<\/li>\n<li>the statement references temporary tables.<\/li>\n<li>Any expression has two arguments without data types<\/li>\n<li>The query creates a permanent table that is then used<\/li>\n<li>It cannot successfully parse the input @tsql<\/li>\n<li>If you supply a known parameter that is not syntactically valid, or you declare any parameter more than one time.<\/li>\n<li>the input batch declares a local variable of the same name as a parameter that you supply in @params.<\/li>\n<\/ul>\n<p>If you try to use an input or output variable twice in a query, it won\u2019t work.<\/p>\n<pre class=\"theme:ssms2012-simple-talk font-size:13 line-height:13 wrap-toggle:false lang:tsql decode:true\">sp_describe_undeclared_parameters N'\r\nSELECT parent.object_id, parent.name, child.name\r\nFROM sys.objects parent\r\n  INNER JOIN sys.objects child \r\n  ON child.parent_object_id = parent.object_id\r\n    AND (child.parent_object_id=@ObjectID\r\n      OR  parent.object_id=@ObjectID)' \r\n<\/pre>\n<p>This gives you an error<\/p>\n<pre class=\"theme:plain-white font-color: red font-size:13 line-height:13 wrap-toggle:false lang:default decode:true \">Msg 11508, Level 16, State 1, Line 142\r\nThe undeclared parameter '@ObjectID' is used more than once in the batch being analyzed.<\/pre>\n<p>This is an odd restriction when one considers that it works fine with <strong>sp_executeSQL <\/strong><\/p>\n<pre class=\"theme:ssms2012-simple-talk font-size:13 line-height:13 wrap-toggle:false lang:tsql decode:true\">sp_executeSQL N'\r\nSELECT parent.object_id, parent.name, child.name\r\nFROM sys.objects parent\r\n  INNER JOIN sys.objects child \r\n  ON child.parent_object_id = parent.object_id\r\n    AND (child.parent_object_id=@ObjectID\r\n      OR  parent.object_id=@ObjectID)',N'@objectid int',@Objectid=94623380\r\n<\/pre>\n<p>&#8230; which gives &#8230;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"453\" height=\"252\" class=\"wp-image-86432\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/02\/word-image-78.png\" \/><\/p>\n<p>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.<\/p>\n<pre class=\"theme:ssms2012-simple-talk font-size:13 line-height:13 wrap-toggle:false lang:tsql decode:true\">exec sp_describe_undeclared_parameters N'\r\nDeclare @ObjectID int=@TheParameter\r\nSELECT parent.object_id, parent.name, child.name\r\n  FROM sys.objects parent\r\n    INNER JOIN sys.objects child \r\n      ON child.parent_object_id = parent.object_id\r\n\t     AND (child.parent_object_id=@ObjectID\r\n\t\t OR  parent.object_id=@ObjectID)'\r\n<\/pre>\n<p>You can cope with output variables too.<\/p>\n<pre class=\"theme:ssms2012-simple-talk font-size:13 line-height:13 wrap-toggle:false lang:tsql decode:true\">exec sp_describe_undeclared_parameters  N'\r\nDeclare @TheDelimiter char(1)=@InputDelimiter\r\nDeclare @TheIdentifier sysname=@InputIdentifier\r\nDeclare @Return sysname\r\nIF @TheDelimiter NOT IN (''&lt;'',''&gt;'',''['','']'',''\"'',''('','')'',''{'',''}'',''`'')\r\n\tSET @TheDelimiter=''[''\r\nIF Coalesce(@TheIdentifier, '''') = ''''\r\n    OR CharIndex(NChar(0xFFFF) COLLATE Latin1_General_100_BIN2, @TheIdentifier) &lt;&gt; 0\r\n     Set @RETURN= ''null'';\r\n IF PatIndex(''%[^a-zA-Z0-9@$#_]%'', @TheIdentifier) &gt; 0\r\n\t Set @RETURN = QuoteName(@TheIdentifier,@TheDelimiter);\r\n    ELSE IF PatIndex(''[@#_]%'', @TheIdentifier) &gt; 0\r\n\t  Set @RETURN= QuoteName(@TheIdentifier,@TheDelimiter);\r\n    ELSE Set @Return=@TheIdentifier\r\nSet @OutputReturn=@Return\r\n<\/pre>\n<p>&#8230; producing &#8230;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"551\" height=\"97\" class=\"wp-image-86433\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/02\/word-image-79.png\" \/><\/p>\n<p>..and yes, it identified the @OutputReturn as an output parameter too.<\/p>\n<p>Fussy? It all works fine with sp_ExecuteSQL without any need for this.<\/p>\n<pre class=\"theme:ssms2012-simple-talk font-size:13 line-height:13 wrap-toggle:false lang:tsql decode:true\">DECLARE @Delimited sysname\r\nEXECUTE sp_executeSQL N'\r\nIF @TheDelimiter NOT IN (''&lt;'',''&gt;'',''['','']'',''\"'',''('','')'',''{'',''}'',''`'')\r\n\tSET @TheDelimiter=''[''\r\nIF Coalesce(@TheIdentifier, '''') = ''''\r\n    OR CharIndex(NChar(0xFFFF) COLLATE Latin1_General_100_BIN2, @TheIdentifier) &lt;&gt; 0\r\n     Set @RETURN= ''null'';\r\n    IF PatIndex(''%[^a-zA-Z0-9@$#_]%'', @TheIdentifier) &gt; 0\r\n\t Set @RETURN = QuoteName(@TheIdentifier,@TheDelimiter);\r\n    ELSE IF PatIndex(''[@#_]%'', @TheIdentifier) &gt; 0\r\n\t  Set @RETURN= QuoteName(@TheIdentifier,@TheDelimiter);\r\n    ELSE Set @Return=@TheIdentifier\r\n\t',\r\n\tN'@TheDelimiter char(1),@TheIdentifier sysname, @Return sysname OUTPUT ',\r\n\t@TheDelimiter=']',@TheIdentifier = 'BusinessEntityContact', @Return =@delimited output \r\nSELECT @delimited\r\n<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"165\" height=\"41\" class=\"wp-image-86434\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/02\/word-image-80.png\" \/><\/p>\n<p>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\u2019t 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.\u00a0<br \/>\nIf you are using this in SSMS, I\u2019d choose to operate the query pane in TEXT more rather than grid and increase the text length to something reasonable (query-&gt;Results to-&gt;).<\/p>\n<pre class=\"theme:ssms2012-simple-talk font-size:13 line-height:13 wrap-toggle:false lang:tsql decode:true\">CREATE OR alter PROCEDURE MakeMyBatchExecute @Stmt NVARCHAR(MAX)\r\n\/**\r\nSummary: &gt;\r\n  This is a simple utility that takes a SQL batch as a \r\n  string, complete with embedded parameters and turns it\r\n  into something that can be executed by SP_ExecuteSQL \r\n  with parameters.\r\n  For example: (I'm not suggestinog you do this)\r\n\r\n  Execute sp_ExecuteSQL N'\r\n  Select convert(int,@This), convert(Datetime2,@That)',\r\n  @params=N'@This int, @That datetime2(7)',\r\n  @This=1, @That=''\r\n  \r\n  ... produces ...\r\n\r\n  Execute sp_ExecuteSQL N'\r\n  Select convert(int,@This), convert(Datetime2,@That)',\r\n  @params=N'@This int, @That datetime2(7)',\r\n  @This=1, @That=''\r\n  \r\n  If it is a parameter that can be coerced into a string\r\n  it suggests a blank string, otherwise it suggests a number\r\n  that can be filled in. It tells you in the @params what\r\n  it thinks the system type is.\r\n  You may need to help the system it uses for detecting the\r\n  type of parameter you are supplying to the batch. I've shown\r\n  two ways of doing this. CAST works fine too.\r\nAuthor: Philip Euripedes Factor\r\nDate: 19\/02\/2020\r\n\r\nExamples:\r\n   -  EXECUTE MakeMyBatchExecute N'\r\n      Declare @ObjectID int=@TheParameter\r\n      SELECT parent.object_id, parent.name, child.name\r\n       FROM sys.objects parent\r\n        INNER JOIN sys.objects child \r\n          ON child.parent_object_id = parent.object_id\r\n\t       AND (child.parent_object_id=@ObjectID\r\n\t\t   OR  parent.object_id=@ObjectID)'\r\n\r\n   -  EXECUTE MakeMyBatchExecute N'\r\n      Select @This+'''',@That+0,@TheOther+'''',@Andanother+0'\r\n\r\n   -  EXECUTE MakeMyBatchExecute N'\r\n      Select convert(int,@This), convert(Datetime2,@That)'\r\n\r\n   -  EXECUTE MakeMyBatchExecute N'\r\n      Select @MyFirstOutput=convert(int,@This), \r\n       @MySecondOutput=convert(numeric(8,2),@That)'\r\n\r\nReturns: &gt;\r\n  a single column result\r\n**\/\r\nAS\r\nDECLARE @Params NVARCHAR(4000);\r\nDECLARE @Tail NVARCHAR(MAX);\r\n\r\nDECLARE @temptable TABLE\r\n  (\r\n  parameter_ordinal INT,\r\n  name NVARCHAR(128),\r\n  suggested_system_type_id INT,\r\n  suggested_system_type_name NVARCHAR(128),\r\n  suggested_max_length SMALLINT,\r\n  suggested_precision TINYINT,\r\n  suggested_scale TINYINT,\r\n  suggested_user_type_id INT,\r\n  suggested_user_type_database NVARCHAR(128),\r\n  suggested_user_type_schema NVARCHAR(128),\r\n  suggested_user_type_name NVARCHAR(128),\r\n  suggested_assembly_qualified_type_name NVARCHAR(4000),\r\n  suggested_xml_collection_id INT,\r\n  suggested_xml_collection_database NVARCHAR(128),\r\n  suggested_xml_collection_schema NVARCHAR(128),\r\n  suggested_xml_collection_name NVARCHAR(128),\r\n  suggested_is_xml_document BIT,\r\n  suggested_is_case_sensitive BIT,\r\n  suggested_is_fixed_length_clr_type BIT,\r\n  suggested_is_input BIT,\r\n  suggested_is_output BIT,\r\n  formal_parameter_name NVARCHAR(128),\r\n  suggested_tds_type_id INT,\r\n  suggested_tds_length INT\r\n  );\r\nINSERT INTO @temptable EXEC sp_describe_undeclared_parameters @Stmt;\r\nSELECT \r\n  @Params =\r\n    String_Agg(\r\n      name + ' ' + suggested_system_type_name + \r\n         CASE WHEN suggested_is_output = 1 THEN ' OUTPUT' ELSE '' END,\r\n      ', '\r\n      ) WITHIN GROUP(ORDER BY parameter_ordinal ASC),\r\n  @Tail =\r\n    String_Agg(\r\n      name + '=' +\r\n\t  CASE \r\n\t    WHEN suggested_is_output = 1 \r\n\t\t  THEN '@OutputVariable' + Convert(VARCHAR(3), parameter_ordinal) + ' OUTPUT'\r\n        WHEN suggested_system_type_id IN (48,52,56,59,60,62,104,106,108,122,127) \r\n\t\t  \/*a number*\/ \r\n\t\t  THEN '1' \r\n\t    ELSE '''''' END,\r\n       ', ' \r\n\t   ) WITHIN GROUP(ORDER BY parameter_ordinal ASC)\r\n  FROM @temptable;\r\nSELECT N'Execute sp_ExecuteSQL N''' + Replace(@Stmt, '''', '''''')\r\n       + ''',\r\n  @params=N''' + Replace(@Params, '''', '''''') + ''',\r\n  '    + @Tail AS [executable];\r\nGO\r\n<\/pre>\n<p>If, for example, we execute this<\/p>\n<pre class=\"theme:ssms2012-simple-talk font-size:13 line-height:13 wrap-toggle:false lang:tsql decode:true\">EXECUTE MakeMyBatchExecute N'\r\n      Declare @ObjectID int=@TheParameter\r\n      SELECT parent.object_id, parent.name, child.name\r\n       FROM sys.objects parent\r\n        INNER JOIN sys.objects child \r\n          ON child.parent_object_id = parent.object_id\r\n\t       AND (child.parent_object_id=@ObjectID\r\n\t\t   OR  parent.object_id=@ObjectID)'\r\n<\/pre>\n<p>We get this as a result\u2026<\/p>\n<pre class=\"theme:plain-white lang:default decode:true \">Execute sp_ExecuteSQL N'\r\n      Declare @ObjectID int=@TheParameter\r\n      SELECT parent.object_id, parent.name, child.name\r\n       FROM sys.objects parent\r\n        INNER JOIN sys.objects child \r\n          ON child.parent_object_id = parent.object_id\r\n\t       AND (child.parent_object_id=@ObjectID\r\n\t\t   OR  parent.object_id=@ObjectID)',\r\n  @params=N'@TheParameter int',\r\n  @TheParameter=1\r\n<\/pre>\n<p>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<\/p>\n<pre class=\"theme:ssms2012-simple-talk font-size:13 line-height:13 wrap-toggle:false lang:tsql decode:true\">Execute sp_ExecuteSQL N'\r\nDeclare @ObjectID int=@TheParameter\r\nSELECT parent.object_id, parent.name, child.name\r\n  FROM sys.objects parent\r\n    INNER JOIN sys.objects child \r\n      ON child.parent_object_id = parent.object_id\r\n\t     AND (child.parent_object_id=@ObjectID\r\n\t\t OR  parent.object_id=@ObjectID)',\r\n  @params=N'@TheParameter int',\r\n  @TheParameter=94623380\r\n<\/pre>\n<p>And it all then works fine<\/p>\n<h1>Summary<\/h1>\n<p>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.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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&#8230;&hellip;<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[6813],"class_list":["post-86431","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/86431","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=86431"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/86431\/revisions"}],"predecessor-version":[{"id":86439,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/86431\/revisions\/86439"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=86431"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=86431"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=86431"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=86431"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}