Thursday, June 15, 2017

Dump a procedure result set into a temporary table by leveraging on sys.dm_exec_describe_first_result_set_for_object function introduced in MS SQL Server 2012

The following script creates a temporary table with attributes defined dynamically based on metadata using sys.dm_exec_describe_first_result_set_for_object function out of a stored procedure.
It populates this table by inserting the recordset returning by this stored procedure.

IF OBJECT_ID('tempdb..##tmp') IS NOT NULL DROP Table ##tmp
GO

DECLARE @strSQL VARCHAR(MAX)

WITH cte AS (
SELECT 'CREATE TABLE ##tmp(' strSQL
UNION ALL
SELECT strSQL = ',' + name + ' ' + system_type_name
FROM sys.dm_exec_describe_first_result_set_for_object
(OBJECT_ID('myschema.dbo.MyProc'), NULL)
UNION ALL
SELECT strSQL = ')')

SELECT @strSQL = COALESCE(@strSQL, '') +  strSQL
FROM cte
WHERE strSQL IS NOT NULL
OPTION (MAXDOP 1);

SET @strSQL = REPLACE(@strSQL,'(,','(');

EXEC (@strSQL);

INSERT INTO ##tmp
EXEC myschema.dbo.MyProc
    5512712        --@Field1    INT
    ....
    ,NULL;        --@FieldN    VARCHAR(15)

SELECT * FROM ##tmp;

No comments:

Post a Comment