What's the easiest way to create a temp table in SQL Server that can hold the result of a stored procedure?

Question

Many times I need to write something like the following when dealing with SQL Server.

create table #table_name
(
    column1 int,
    column2 varchar(200)
    ...
)

insert into #table_name
execute some_stored_procedure;

But create a table which has the exact syntax as the result of a stored procedure is a tedious task. For example, the result of sp_helppublication has 48 columns! I want to know whether there is any easy way to do this.

Thanks.

Answer 1

If the procedure just returns one result set and the ad hoc distributed queries option is enabled.

SELECT * 
INTO #T 
FROM OPENROWSET('SQLNCLI', 
                'Server=(local)\MSSQL2008;Trusted_Connection=yes;',
                 'SET FMTONLY OFF;EXEC sp_who')

Or you can set up a loopback linked server and use that instead.

EXEC sp_addlinkedserver @server = 'LOCALSERVER',  @srvproduct = '',
                        @provider = 'SQLNCLI', @datasrc = @@servername

SELECT *
INTO  #T
FROM OPENQUERY(LOCALSERVER, 
               'SET FMTONLY OFF;
               EXEC sp_who')

Answer 2

No. The result of a stored procedure can vary wildly: it isn't designed to always return exactly one result set like a SELECT on some object.

You have to execute CREATE TABLE

Answer 3

In SQL Server 2012, you will be able to do this locally, assuming the result set you are after is the first result:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += ',' + CHAR(13) + CHAR(10) + CHAR(9)
    + name + ' ' + system_type_name
    FROM sys.dm_exec_describe_first_result_set('sp_who', NULL, 1);

SELECT @sql = N'CREATE TABLE #f
(' + STUFF(@sql, 1, 1, N'') + '
);';

PRINT @sql;

Result:

CREATE TABLE #f
(
    spid smallint,
    ecid smallint,
    status nchar(30),
    loginame nvarchar(128),
    hostname nchar(128),
    blk char(5),
    dbname nvarchar(128),
    cmd nchar(16),
    request_id int
);

Note there is a limitation: if your stored procedure creates #temp tables, the metadata functionality does not work. This is why I did not use sp_who2. :-)