SQL Server Portal

We usually use temporary table to keep temporary data in the stored procedure, it can be local, global or a table variable. Recently, I was working on the stored procedures’ metadata in a client database using sp_describe_first_result_set (a new system stored procedure shipped with SQL Server 2012). Ooopps….. there’s an error, let me explain the error in detail. This error message is available in SQL Server 2012 and above.

Message Number: 11526

Severity : 16

Error Message: The metadata could not be determined because statement ‘%.*ls’ in procedure ‘%.*ls’ uses a temp table.

Error Generation:
Let me create a stored procedure to demonstrate this error.

USE tempdb
GO
--DROP PROCEDURE [dbo].[usp_sample]
--GO
CREATE PROCEDURE [dbo].[usp_sample]
AS
BEGIN
CREATE TABLE #temp_Sample
(
[ID] INT,
[NAME] VARCHAR(32)
)
INSERT INTO #temp_Sample VALUES (1,'Bob')

SELECT * FROM #temp_Sample
END
GO

EXEC [dbo].[usp_sample]
GO
--OUTPUT

You can see that I have created a stored procedure and executed it as well and got the result set. 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

USE tempdb
GO
Sp_describe_first_result_set @tsql =N'usp_sample'
GO
--OUTPUT

Msg 11526, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement ‘INSERT INTO #temp_Sample VALUES (1,’Bob’)’ in procedure ‘usp_sample’ uses a temp table.

Ooopps…… I am unable to execute it.

Resolution:
The reason behind this error is, you used a local temporary table in the stored procedure and you cannot view the metadata using sp_describe_first_result_set. So what to do ? Very simple guys, just convert local temporary table to table variable and view its metadata.
Given below is the correct script.

USE tempdb
GO
--DROP PROCEDURE [dbo].[usp_sample]
--GO
CREATE PROCEDURE [dbo].[usp_sample]
AS
BEGIN
DECLARE @temp_Sample TABLE
(
[ID] INT,
[NAME] VARCHAR(32)
)
INSERT INTO @temp_Sample VALUES (1,'Bob')

SELECT * FROM @temp_Sample
END
GO

Sp_describe_first_result_set @tsql =N'usp_sample'
GO
--OUTPUT

Conclusion :
Remember, whenever you use temporary table in the stored procedure and would like to view the metadata of that stored procedure using sp_describe_first_result_set,  always convert local temporary table to table variable.

Read Full Post »