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.
sp_describe_undeclared_parameters N'
SELECT parent.object_id, parent.name, child.name
FROM sys.objects parent
INNER JOIN sys.objects child
ON child.parent_object_id = parent.object_id
AND (child.parent_object_id=@ObjectID
OR parent.object_id=@ObjectID)'
This gives you an error
Msg 11508, Level 16, State 1, Line 142 The undeclared parameter '@ObjectID' is used more than once in the batch being analyzed.
This is an odd restriction when one considers that it works fine with sp_executeSQL
sp_executeSQL N'
SELECT parent.object_id, parent.name, child.name
FROM sys.objects parent
INNER JOIN sys.objects child
ON child.parent_object_id = parent.object_id
AND (child.parent_object_id=@ObjectID
OR parent.object_id=@ObjectID)',N'@objectid int',@Objectid=94623380
… 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.
exec sp_describe_undeclared_parameters N'
Declare @ObjectID int=@TheParameter
SELECT parent.object_id, parent.name, child.name
FROM sys.objects parent
INNER JOIN sys.objects child
ON child.parent_object_id = parent.object_id
AND (child.parent_object_id=@ObjectID
OR parent.object_id=@ObjectID)'
You can cope with output variables too.
exec sp_describe_undeclared_parameters N'
Declare @TheDelimiter char(1)=@InputDelimiter
Declare @TheIdentifier sysname=@InputIdentifier
Declare @Return sysname
IF @TheDelimiter NOT IN (''<'',''>'',''['','']'',''"'',''('','')'',''{'',''}'',''`'')
SET @TheDelimiter=''[''
IF Coalesce(@TheIdentifier, '''') = ''''
OR CharIndex(NChar(0xFFFF) COLLATE Latin1_General_100_BIN2, @TheIdentifier) <> 0
Set @RETURN= ''null'';
IF PatIndex(''%[^a-zA-Z0-9@$#_]%'', @TheIdentifier) > 0
Set @RETURN = QuoteName(@TheIdentifier,@TheDelimiter);
ELSE IF PatIndex(''[@#_]%'', @TheIdentifier) > 0
Set @RETURN= QuoteName(@TheIdentifier,@TheDelimiter);
ELSE Set @Return=@TheIdentifier
Set @OutputReturn=@Return
… 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.
DECLARE @Delimited sysname
EXECUTE sp_executeSQL N'
IF @TheDelimiter NOT IN (''<'',''>'',''['','']'',''"'',''('','')'',''{'',''}'',''`'')
SET @TheDelimiter=''[''
IF Coalesce(@TheIdentifier, '''') = ''''
OR CharIndex(NChar(0xFFFF) COLLATE Latin1_General_100_BIN2, @TheIdentifier) <> 0
Set @RETURN= ''null'';
IF PatIndex(''%[^a-zA-Z0-9@$#_]%'', @TheIdentifier) > 0
Set @RETURN = QuoteName(@TheIdentifier,@TheDelimiter);
ELSE IF PatIndex(''[@#_]%'', @TheIdentifier) > 0
Set @RETURN= QuoteName(@TheIdentifier,@TheDelimiter);
ELSE Set @Return=@TheIdentifier
',
N'@TheDelimiter char(1),@TheIdentifier sysname, @Return sysname OUTPUT ',
@TheDelimiter=']',@TheIdentifier = 'BusinessEntityContact', @Return =@delimited output
SELECT @delimited
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->).
CREATE OR alter PROCEDURE MakeMyBatchExecute @Stmt NVARCHAR(MAX)
/**
Summary: >
This is a simple utility that takes a SQL batch as a
string, complete with embedded parameters and turns it
into something that can be executed by SP_ExecuteSQL
with parameters.
For example: (I'm not suggestinog you do this)
Execute sp_ExecuteSQL N'
Select convert(int,@This), convert(Datetime2,@That)',
@params=N'@This int, @That datetime2(7)',
@This=1, @That=''
... produces ...
Execute sp_ExecuteSQL N'
Select convert(int,@This), convert(Datetime2,@That)',
@params=N'@This int, @That datetime2(7)',
@This=1, @That=''
If it is a parameter that can be coerced into a string
it suggests a blank string, otherwise it suggests a number
that can be filled in. It tells you in the @params what
it thinks the system type is.
You may need to help the system it uses for detecting the
type of parameter you are supplying to the batch. I've shown
two ways of doing this. CAST works fine too.
Author: Philip Euripedes Factor
Date: 19/02/2020
Examples:
- EXECUTE MakeMyBatchExecute N'
Declare @ObjectID int=@TheParameter
SELECT parent.object_id, parent.name, child.name
FROM sys.objects parent
INNER JOIN sys.objects child
ON child.parent_object_id = parent.object_id
AND (child.parent_object_id=@ObjectID
OR parent.object_id=@ObjectID)'
- EXECUTE MakeMyBatchExecute N'
Select @This+'''',@That+0,@TheOther+'''',@Andanother+0'
- EXECUTE MakeMyBatchExecute N'
Select convert(int,@This), convert(Datetime2,@That)'
- EXECUTE MakeMyBatchExecute N'
Select @MyFirstOutput=convert(int,@This),
@MySecondOutput=convert(numeric(8,2),@That)'
Returns: >
a single column result
**/
AS
DECLARE @Params NVARCHAR(4000);
DECLARE @Tail NVARCHAR(MAX);
DECLARE @temptable TABLE
(
parameter_ordinal INT,
name NVARCHAR(128),
suggested_system_type_id INT,
suggested_system_type_name NVARCHAR(128),
suggested_max_length SMALLINT,
suggested_precision TINYINT,
suggested_scale TINYINT,
suggested_user_type_id INT,
suggested_user_type_database NVARCHAR(128),
suggested_user_type_schema NVARCHAR(128),
suggested_user_type_name NVARCHAR(128),
suggested_assembly_qualified_type_name NVARCHAR(4000),
suggested_xml_collection_id INT,
suggested_xml_collection_database NVARCHAR(128),
suggested_xml_collection_schema NVARCHAR(128),
suggested_xml_collection_name NVARCHAR(128),
suggested_is_xml_document BIT,
suggested_is_case_sensitive BIT,
suggested_is_fixed_length_clr_type BIT,
suggested_is_input BIT,
suggested_is_output BIT,
formal_parameter_name NVARCHAR(128),
suggested_tds_type_id INT,
suggested_tds_length INT
);
INSERT INTO @temptable EXEC sp_describe_undeclared_parameters @Stmt;
SELECT
@Params =
String_Agg(
name + ' ' + suggested_system_type_name +
CASE WHEN suggested_is_output = 1 THEN ' OUTPUT' ELSE '' END,
', '
) WITHIN GROUP(ORDER BY parameter_ordinal ASC),
@Tail =
String_Agg(
name + '=' +
CASE
WHEN suggested_is_output = 1
THEN '@OutputVariable' + Convert(VARCHAR(3), parameter_ordinal) + ' OUTPUT'
WHEN suggested_system_type_id IN (48,52,56,59,60,62,104,106,108,122,127)
/*a number*/
THEN '1'
ELSE '''''' END,
', '
) WITHIN GROUP(ORDER BY parameter_ordinal ASC)
FROM @temptable;
SELECT N'Execute sp_ExecuteSQL N''' + Replace(@Stmt, '''', '''''')
+ ''',
@params=N''' + Replace(@Params, '''', '''''') + ''',
' + @Tail AS [executable];
GO
If, for example, we execute this
EXECUTE MakeMyBatchExecute N'
Declare @ObjectID int=@TheParameter
SELECT parent.object_id, parent.name, child.name
FROM sys.objects parent
INNER JOIN sys.objects child
ON child.parent_object_id = parent.object_id
AND (child.parent_object_id=@ObjectID
OR parent.object_id=@ObjectID)'
We get this as a result…
Execute sp_ExecuteSQL N'
Declare @ObjectID int=@TheParameter
SELECT parent.object_id, parent.name, child.name
FROM sys.objects parent
INNER JOIN sys.objects child
ON child.parent_object_id = parent.object_id
AND (child.parent_object_id=@ObjectID
OR parent.object_id=@ObjectID)',
@params=N'@TheParameter int',
@TheParameter=1
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
Execute sp_ExecuteSQL N'
Declare @ObjectID int=@TheParameter
SELECT parent.object_id, parent.name, child.name
FROM sys.objects parent
INNER JOIN sys.objects child
ON child.parent_object_id = parent.object_id
AND (child.parent_object_id=@ObjectID
OR parent.object_id=@ObjectID)',
@params=N'@TheParameter int',
@TheParameter=94623380
And it all then works fine
Summary
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.
The post Grappling with sp_describe_undeclared_parameters(). The Hows, Whys and Wherefores. appeared first on Simple Talk.
from Simple Talk https://ift.tt/2P80k0e
via
No comments:
Post a Comment