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.

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)