How can I select column headers returned from EXEC statement into a temp table?

Question

I'm trying to select the column headers/names from an EXEC statement in SQL.

For example, if I run the code

SET @ls_SQL = 'EXEC dbo.Generic_Proc ' + @Param
    EXEC(@ls_SQL)

and it returns:

|---------------------|------------------|
|      ColumnName1    |     ColumnName2  |
|---------------------|------------------|
|          12         |         34       |
|---------------------|------------------|

How can I get the strings 'ColumnName1' and 'ColumnName2' into a temporary table? Something like:

|---------------------|------------------|
|        Row          |       Header     |
|---------------------|------------------|
|          1          |    ColumnName1   |
|---------------------|------------------|
|          2          |    ColumnName2   |
|---------------------|------------------|

I tried using sp_describe_first_result_set, but 'Generic_Proc' is using dynamic SQL, so I get an error. The error states that I can explicitly describe the result set, but unfortunately the returned columns will be different depending on the parameter sent to it. Is there any way around this?

Penina · Answer 1 · 2020-06-18T19:50:46.583

I'd recommend using UNPIVOT for this. To do an unpivot dynamically, I'd put the results from your stored procedure into a temp table.

Then here's the sql that will take care of the rest:

SELECT  1 AS Column1, 2 AS Column2, 3 AS Column3, 4 AS Column4
INTO #Temp --Put your SP results here


DECLARE @columnsToUnpivot  AS NVARCHAR(MAX),
   @unpivotQuery  AS NVARCHAR(MAX)

SELECT @columnsToUnpivot 
  = STUFF((SELECT ','+QUOTENAME(name)
  FROM Tempdb.Sys.Columns Where Object_ID = Object_ID('tempdb..#Temp')
           for xml path('')), 1, 1, '')
SELECT @columnsToUnpivot
set @unpivotQuery 
  = ' SELECT
        *
    FROM
        #Temp AS t
     UNPIVOT
     (
        VALUE
        for Col in ('+ @columnsToUnpivot +')
     ) u'


exec sp_executesql @unpivotQuery;
DROP TABLE #Temp

score 0 · Answer 2 · answered Jun 18 '20 at 19:26

I'm trying to select the column headers/names from an EXEC statement in SQL . . . [but] the returned columns will be different depending on the parameter sent to it.

There is no first-class way in TSQL to do that.

You can do that in SQL CLR, or by introducing a Loopback Linked Server, with the caveat that

Loopback linked servers are intended for testing and are not supported for many operations, such as distributed transactions.

Linked Servers

select *
into #foo
from openquery(Loopback,'select 1 a, 3 b')

select c.name, t.name type_name
from tempdb.sys.columns c
join tempdb.sys.types t
  on c.system_type_id = t.system_type_id
where object_id = object_id('tempdb..#foo')

How can I select column headers returned from EXEC statement into a temp table?

2 Answers2