SQL SERVER 2012 – Fix – Error :11514 – The metadata could not be determined because statement in procedure contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.

Feeds:: Posts; Comments

« SQL SERVER 2012 – FORCESEEK Table Hint – Enhancement

SQL SERVER – Get day of week in SQL 2005/2008/2012 »

SQL SERVER 2012 – Fix – Error :11514 – The metadata could not be determined because statement in procedure contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.

April 17, 2013 by Muhammad Imran

Sometimes, its essential to use dynamic SQL when there are different queries for different scenarios, to build the query dynamically. 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: 11514

Severity : 16

Error Message: The metadata could not be determined because statement ‘%.*ls’ in procedure ‘%.*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.

Use tempdb
Go
Create Procedure UDP_Resultset
As
Declare @FirstLetter varchar(3)
Declare @SecondLetter varchar(3)

Set @FirstLetter ='''A'''
Set @SecondLetter ='''B'''

Declare @String varchar(Max)

Set @String ='Select ' + @FirstLetter +' As [First Letter]'
EXEC (@String)

Set @String ='Select ' + @SecondLetter +' As [Second Letter]'
EXEC (@String)
GO
EXEC UDP_Resultset
--OUTPUT

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

EXEC Sp_describe_first_result_set @tsql =N'EXEC UDP_Resultset'
--OUTPUT

Msg 11514, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement ‘EXEC (@String)’ in procedure ‘UDP_Resultset’ 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, we have dynamic SQL inside the SQL stored procedure and whenever we need to view the metadata of stored procedure having dynamic SQL, we must use the result set clause along with stored procedure.
Given below is the correct script.

Sp_describe_first_result_set @tsql =
N'EXEC UDP_Resultset WITH RESULT SETS (([1st Letter] varchar(50))
,([2nd Letter] varchar(50)));'
GO
--OUTPUT

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