I like challenges and when we are faced with a challenge – I just cannot sleep. I am in constant search for solution. More importantly I look forward for the learning experience these bring to me. Recently a senior developer was talking to me and got me thinking.
Senior Developer: Hey Pinal.
Pinal: Yes, tell me.
Senior Developer: I wanted to ask you something from SQL Server point of view.
Pinal: Sure, go ahead. Let me check if I know it.
Senior Developer: I have seen your session where you said, inappropriate data types while comparing can get into trouble in terms of performance.
Pinal: Yes, you want to see a demo?
Senior Developer: No, I understand your point of view. Having said that, I have a number of junior developers joined my team.
Pinal: Ok, now is that a problem?
Senior Developer: hahaha, no Pinal. That is not the problem. Since our code has a number of Dynamic SQL’s as part of our code. We have made sure we use parameterization techniques of sp_executesql.
Pinal: That is nice.
Senior Developer: Here is the problem, we still see our developers use these sp_executesql but they pass the parameters with wrong data types unknowingly. And I see sub-optimal plans being generated and hampering our performance from time to time during code review process.
Pinal: Ok, what is the help you need from me?
Senior Developer: Is there a way for my developers to validate if they are using the right data type when they are executing their Dynamic SQL inside SQL Server?
Pinal: Oh sure, if you are on SQL Server 2012 and above – there is a neat trick. But it involves your developers do an extra step of validating their parameter data types. Is that ok?
Senior Developer: Oh sure. Please tell me – I am all ears and will add it to our code review process and unit testing by developers.
Pinal: Don’t worry, I am going to write it as a blog post.
Knowing sp_describe_undeclared_parameters
This stored procedure returns a result set that contains metadata about undeclared parameters in a TSQL batch. It considers each parameter that is used in the @tsql batch, but was not declared in @params. It returns 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.
The template query is:
sp_describe_undeclared_parameters @tsql, @params
MSDN Documentation: sp_describe_undeclared_parameters (Transact-SQL)
Let me start by showing a Dynamic SQL where we are not sure of the parameters and what datatypes are for the statement finally. If we execute the statement as-is this will give us an error.
sp_executesql N'SELECT object_id, name, type_desc
FROM sys.indexes
WHERE object_id = @id OR NAME = @name OR type_desc = @typedesc'
The error we will get is:
Msg 137, Level 15, State 2, Line 3 Must declare the scalar variable "@id".
Now if the developer wants to know what the datatypes of each of the parameters, then he can use the sp_describe_undeclared_parameters stored procedure. To start with, don’t pass the @params list.
EXECUTE sp_describe_undeclared_parameters @tsql =
N'SELECT object_id, name, type_desc
FROM sys.indexes
WHERE object_id = @id OR NAME = @name OR type_desc = @typedesc'
This will output 3 rows. One for each of the parameter defined. It deduces the type information. And this is super cool if you have never used.
Now when I showed this to the Senior Developer, he was pleasantly surprised that SQL Server can do a great job is helping us get the code right.
If you add the @params with possible parameters, those are not displayed in the output. I would generally send it without the parameter list to know the complete sequence. This is a great way to learn from SQL Server.
Do let me know if you found this useful and if you are planning to use the same in your environments.
Reference: Pinal Dave (https://blog.sqlauthority.com)
5 Comments. Leave new
Very helpful.
That’s excellent! Thanks for the tip.
Now if only third party tools (report writers, etc) could start taking advantage of this – I often find their parameter sniffing code to be less than ideal and there’s often no way to turn off the sniffing leading to ugly workaround hacks :)
Is there an option for SQL 2008?
Eduardo – No. This proc was introduced in SQL 2012.
Why, sp_sproc_columns_100 query not able to fetch meta data information from SQLServer2012 server. Is it mandatory to use “sp_describe_undeclared_parametrs” query to get meta data information of stored procedure/