SQL Server Portal

Dynamic SQL queries are one of my favorite queries when it comes to handling multiple conditions from a single query. But it should be handled very carefully. The error we will discuss in this article is related to dynamic SQL and sp_describe_first_result_set, a new system stored procedure shipped with SQL Server 2012. This error message is available in SQL Server 2012 and above.

Let’s discuss this in detail:
Message Number: 11513

Severity : 16

Error Message: The metadata could not be determined because statement ‘%.*ls’ contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.

Error Generation:
Let me create a sample to demonstrate this error. In this sample, I need to dynamically select few columns from [HumanResources].[vEmployee] (View).

USE AdventureWorks2012
GO
DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @SelectClause AS NVARCHAR(MAX)
SET @SelectClause='TOP 10 [FirstName],[EmailAddress]'
SET @SQL='SELECT '+ @SelectClause +
' FROM [HumanResources].[vEmployee]'
EXEC (@SQL)
GO
--OUTPUT

You can see that the above script executed successfully. Now I would like to view the metadata of the above dynamic SQL query. Given below is the script to view the metadata using sp_describe_first_result_set

sp_describe_first_result_set @tsql =N'
DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @SelectClause AS NVARCHAR(MAX)
SET @SelectClause=N''TOP 10 [FirstName],[EmailAddress]''
SET @SQL=N''SELECT '''''' + @SelectClause
+ '''''' FROM [HumanResources].[vEmployee] ''
EXEC(@SQL)'
GO
--OUTPUT

Msg 11513, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement ‘EXEC(@SQL)’ contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.

Ooopps…… I am unable to execute it.

Resolution:
The reason behind this error is, this query is developed using dynamic SQL and to view the metadata of dynamic SQL query, we must use the result set clause with dynamic SQL query.

Given below is the correct script.

sp_describe_first_result_set @tsql =N'
DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @SelectClause AS NVARCHAR(MAX)
SET @SelectClause=N''TOP 10 [FirstName],[EmailAddress]''
SET @SQL=N''SELECT '''''' + @SelectClause
+ '''''' FROM [HumanResources].[vEmployee] ''

EXEC(@SQL) WITH RESULT SETS
(([FirstName] varchar(50)
,[EmailAddress] nvarchar(50)));'
GO
--OUTPUT

Conclusion :
Remember, whenever you use dynamic SQL query and would like to view the metadata of that query using sp_describe_first_result_set,  always use result set clause.

Read Full Post »