SQL Server Portal

In the earlier versions of SQL Server, we usually used SET FMTONLY to grab the metadata of the query. In SQL Server 2012, a new system stored procedure shipped namely sp_describe_first_result_set to browse the metadata of the first possible result set of the queries. Today, I will discuss a new error message shipped in SQL Server 2012 which I came across when I was trying to get the metadata of a query having variables.

Let’s discuss this in detail:

Message Number: 11521

Severity : 16

Error Message: The metadata could not be determined because statement ‘%.*ls’ uses an undeclared parameter in a context that affects its metadata.

Error Generation:
Let me create a sample to demonstrate this error. In this sample, I tried to pass a variable with its value along with the query and it executed successfully.
Given below is the script and its output.

USE AdventureWorks2012
GO
DECLARE @Country as varchar(100)
SET @Country='United Arab Emirates'
SELECT @Country as [Country], CountryRegionCode,CurrencyCode
FROM [Sales].[CountryRegionCurrency]
WHERE CountryRegionCode='AE'
GO
--OUTPUT

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

USE AdventureWorks2012
GO
sp_describe_first_result_set @tsql =N'
SELECT @Country as [Country], CountryRegionCode,CurrencyCode FROM [Sales].[CountryRegionCurrency]
WHERE CountryRegionCode=''AE'''
GO
--OUTPUT

Msg 11521, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement ‘SELECT @Country as [Country], CountryRegionCode,CurrencyCode FROM [Sales].[CountryRegionCurrency] W’ uses an undeclared parameter in a context that affects its metadata.

Ooopps…… I am unable to get the metadata of the above query.

Resolution:
The reason behind this error is, I declared a variable and passed it in the above query at the time of execution but when I tried to get its metadata of the first possible result set, I did not declare the variable within sp_describe_first_result_set. Because I thought that only the query is enough to get the metadata information. Make sure that if you pass any variable into the query, you also declare it at the time of metadata retrieving using sp_describe_first_result_set. Let’s correct the script and declare the variable.

Given below is the correct script.

USE AdventureWorks2012
GO
sp_describe_first_result_set @tsql =N'
DECLARE @Country as varchar(100)
SELECT @Country as [Country], CountryRegionCode,CurrencyCode FROM [Sales].[CountryRegionCurrency]
WHERE CountryRegionCode=''AE'''
GO
GO
--OUTPUT

Conclusion :
Remember, whenever you use any SQL query having variables and would like to view the metadata of the first possible result set of that query using sp_describe_first_result_set,  always declare the variables used in the query.