SQL SERVER – Identifying T-SQL Missing Parameter Data Types with SQL Server

SQL SERVER - Identifying T-SQL Missing Parameter Data Types with SQL Server helpbook 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.

SQL SERVER - Identifying T-SQL Missing Parameter Data Types with SQL Server sp_describe_undeclared_parameters-01

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)

Previous Post
SQL SERVER – Proof of Concepts with Windows Azure – Notes from the Field #056
Next Post
SQL SERVER – Customization of New Query in SQL Server Management Studio

Related Posts

No results found.

5 Comments. Leave new

  • ashishgilhotraGilhotra
    November 28, 2014 3:18 pm

    Very helpful.

    Reply
  • Ian Yates (@IanYates82)
    December 3, 2014 7:09 am

    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 :)

    Reply
  • Is there an option for SQL 2008?

    Reply
  • 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/

    Reply

Leave a Reply

Menu