As a SQL Server developer, it is important to ensure that the code we write performs optimally. One common issue that can impact performance is the use of inappropriate data types while comparing values. However, even if we use parameterization techniques like sp_executesql, there can still be instances where developers unknowingly pass parameters with incorrect data types, resulting in sub-optimal execution plans and performance issues.

In this blog post, we will explore a neat trick in SQL Server 2012 and above that can help developers validate the data types of parameters used in Dynamic SQL statements.

The trick involves using the stored procedure sp_describe_undeclared_parameters. This procedure returns a result set containing metadata about undeclared parameters in a TSQL batch. It considers each parameter used in the batch but not declared in the @params list. It returns one row for each undeclared parameter, providing deduced type information for that parameter.

Let’s take a look at an example to understand how this works. Consider the following Dynamic SQL statement:

sp_executesql N'SELECT object_id, name, type_desc FROM sys.indexes WHERE object_id = @id OR NAME = @name OR type_desc = @typedesc'

If we execute this statement as-is, we will encounter an error: Msg 137, Level 15, State 2, Line 3 Must declare the scalar variable "@id". This error occurs because the parameter @id is not declared.

To determine the data types of the parameters, we can use the sp_describe_undeclared_parameters stored procedure. Simply execute the following command:

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 three rows, one for each parameter defined in the statement. The procedure deduces the type information for each parameter, allowing developers to validate if they are using the correct data types.

It is worth noting that if you include the @params list with possible parameters, those parameters will not be displayed in the output. To get the complete sequence of parameters, it is recommended to execute the procedure without the @params list.

Using sp_describe_undeclared_parameters can be a great way to ensure that your Dynamic SQL statements have the correct parameter data types, leading to improved performance and optimized execution plans.

I hope you found this trick useful and consider incorporating it into your development process. Let me know if you have any questions or if you plan to use this technique in your environments.