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

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. His question was about identifying missing parameter data types in T-SQL.

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.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.