By Just a learner


2012-02-13 08:10:42 8 Comments

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.

4 comments

@Aaron Bertrand 2012-02-13 18:04:13

In SQL Server 2012 and above, you can use sys.dm_exec_describe_first_result_set 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. :-)

@Ross Presser 2019-08-22 15:32:56

Is the SELECT @sql += *expression* syntax documented somewhere? should an ORDER BY be included to make it stable?

@Aaron Bertrand 2019-08-22 15:53:01

@Ross yes, it was introduced in SQL Server 2008, and is documented here. ORDER BY is actually known to make this less stable. If you want the results in a predictable order, use FOR XML PATH or, if on a new enough version of SQL Server, STRING_AGG.

@Ross Presser 2019-08-22 20:03:04

Slight correction: you linked to arithmetic += ... string += is documented here. But thank you!

@Aaron Bertrand 2019-08-22 20:59:24

@Ross yup, sorry.

@Knickerless-Noggins 2015-08-04 13:47:05

I would write a procedure to generate the table for me:

CREATE PROCEDURE [dbo].[p_create_table_from_procedure]
    @TABLE_NAME AS NVARCHAR(MAX),
    @PROCEDURE_NAME AS NVARCHAR(MAX)

As
    DECLARE @CREATE_TABLE_QUERY NVARCHAR(MAX) = N'';


    SELECT 
        @CREATE_TABLE_QUERY += ', ' + name + ' ' + UPPER(system_type_name) + CHAR(13) + CHAR(10) + CHAR(9)

    FROM 
        sys.dm_exec_describe_first_result_set(@procedure_name, NULL, 1);


    SELECT 
        @CREATE_TABLE_QUERY = N'CREATE TABLE ' + @table_name + '(' + CHAR(13) + CHAR(10) + CHAR(9) + STUFF(@CREATE_TABLE_QUERY, 1, 1, N'') + ');';

    PRINT @CREATE_TABLE_QUERY;

Then call it with:

EXEC p_create_table_from_procedure 'YOUR_TABLE_NAME_HERE', 'YOUR_PROCEDURE_NAME_HERE'

Note: Replace 'YOUR_PROCEDURE_NAME_HERE' with the name of your own stored procedure.

Note: Replace YOUR_TABLE_NAME_HERE with the table name of your choice.

The above will generate something like this:

CREATE TABLE YOUR_TABLE_NAME_HERE(
     WeekName VARCHAR(40)
    , Line Name VARCHAR(50)
    , TheDate DATETIME
    , ReceivedAll INT
    , Answered INT
    , Abandoned INT
    , Call Length INT
    , WaitTimeAnswer INT
    , WaitTimeAbandon INT
    , PeriodName VARCHAR(10)
    , Week SMALLINT
    , Period SMALLINT
    , Year SMALLINT
    , WeekInPeriod SMALLINT
    , NumWeeksInPeriod SMALLINT
    , WeekendDate DATETIME
    , CRCOperative VARCHAR(100)
    , CallType VARCHAR(20)
    , Charge Time INT
    , SourceNumber VARCHAR(80)
    , DestinationNumber VARCHAR(80)
    , CallStart DATETIME
    , Out of Hours VARCHAR(12)
    , IsWorkingDay BIT
    );

@Max Vernon 2015-08-05 04:03:25

How is this different from @AaronBertrand's answer above?

@Martin Smith 2012-02-13 09:29:55

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')

@Andreas Ågren 2012-02-13 10:01:45

Don't you mean SET FMT_ONLY ON?

@Martin Smith 2012-02-13 10:15:45

@Andreas - No because I assumed the idea was to both create and populate the table from the stored procedure output.

@gbn 2012-02-13 08:33:27

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

Related Questions

Sponsored Content

1 Answered Questions

1 Answered Questions

2 Answered Questions

[SOLVED] How to do ALTER TABLE TYPE efficiently

2 Answered Questions

[SOLVED] Truncating table without using Alter Table permission

2 Answered Questions

[SOLVED] SQL Server : create table within stored procedure

1 Answered Questions

1 Answered Questions

[SOLVED] Create Temp Table From Stored Procedure Result

  • 2013-03-19 19:12:20
  • Juan Velez
  • 5995 View
  • 3 Score
  • 1 Answer
  • Tags:   sql-server

Sponsored Content