Creating Temp Table OR Table Variable by executing a stored procedure with parameters

• skb 44459

  SSCarpal Tunnel

  Points: 4768

  More actions

  January 11, 2018 at 5:54 am

  #334473

  Is there any way of Creating Temp Table OR Table Variable by executing a stored procedure with parameters.

  I would like to do something like

  declare @Parm1 varchar(10)
  declare @Parm1 varchar(10)

  select * into report from
  EXEC CustomerSP @Parm1, @Parm2, @Parm3

  OR
  select * from
  EXEC CustomerSP @Parm1, @Parm2, @Parm3

• Thom A

  SSC Guru

  Points: 99126

  More actions

  January 11, 2018 at 6:04 am

  #1975483

  You can't use INTO, but you can define your table, and then INSERT:

  CREATE PROC testSP AS
    SELECT 1 AS ID;
GO

  CREATE TABLE #test(ID int);

  INSERT INTO #test
EXEC TestSP;

  SELECT *
FROM #test;
GO

  DROP PROC testSP;
GO


  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 11, 2018 at 6:11 am

  #1975484

  Following on from Thom's post, you can use sp_describe_first_result_set to get the structure for your temp table.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  January 11, 2018 at 7:45 am

  #1975500

  ChrisM@Work - Thursday, January 11, 2018 6:11 AM

  Following on from Thom's post, you can use sp_describe_first_result_set to get the structure for your temp table.

  Don't have the latest version but you can try this one for size
  😎

  USE TEEST;
GO
SET NOCOUNT ON;

  GO
ALTER FUNCTION dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY
(
  @TABLE_NAME   NVARCHAR(128)
 ,@SCHEMA_NAME   NVARCHAR(128)
 ,@EXEC_SQL    NVARCHAR(MAX)
 ,@PARAMETER_STR  NVARCHAR(MAX)
 ,@IS_TABLE_VARIABLE BIT
 ,@IS_TEMPORARY_TABLE BIT
 ,@CREATE_INSERT_DML TINYINT
)
RETURNS TABLE
AS
/******************************************************************************
-------------------------------------------------------------------------------
OBJECT NAME: dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY
-------------------------------------------------------------------------------
OBJECT VERSION: 1.5.42678
-------------------------------------------------------------------------------
OUTPUT: TABLE_DDL NVARCHAR(MAX)
-------------------------------------------------------------------------------
PLATFORM: SQL SERVER 2012 AND LATER (ALL EDITIONS)
-------------------------------------------------------------------------------
PARAMETERS:
NAME      Data type  Desciption
@TABLE_NAME   NVARCHAR(128) The name of the target table

  @SCHEMA_NAME   NVARCHAR(128) Target schema name

  @EXEC_SQL    NVARCHAR(MAX) A valid sql statement that returns a result
            set. The user must have permissions to run
            the statement, otherwise the function will
            not return any output.

  @PARAMETER_STR  NVARCHAR(MAX) Parameter(s) for the sql statement in the
            same format as for the sp_executesql system
            procedure.

  @IS_TABLE_VARIABLE BIT    Generate table variable DDL
            0 The function does not return a table
             variable DDL
            1 The function returns a table variable DDL
             and prefixes the name passed in the
             @TABLE_NAME with "@"

  @IS_TEMPORARY_TABLE BIT    Generate temporary table DDL

  @CREATE_INSERT_DML TINYINT   Appends an insert statement to the output
            0 No insert statement generated
            1 Appends an insert statement to the output
            2 Appends an insert statement to the output
             and adds the statement passed in the
             @EXEC_SQL parameter to the output.
-------------------------------------------------------------------------------
AUTHOR: Eirikur Eiriksson
   http://www.eesql.com
-------------------------------------------------------------------------------
This code is provided as is, without any warranty. It is free to use for any
legitimate purposes, given that the whole and unedited comment section is
included.
-------------------------------------------------------------------------------
COPYRIGHT © 2012 - 2016 Eirikur Eiriksson
-------------------------------------------------------------------------------
DESCRIPTION:
-------------------------------------------------------------------------------
The function returns a DDL for any executable expression that returns a result
set with named columns. Columns with no name (No column name) will be ignored
and will not be present in the generated DDL.

  The function can generate all three types of table DDL by setting the
@IS_TABLE_VARIABLE and @IS_TEMPORARY_TABLE parameters:

  DDL Type      @IS_TABLE_VARIABLE @IS_TEMPORARY_TABLE
----------------------------------------------------------------
create table     0       0
create temporary table 0       1
declare table variable 1       0
NULL       1       1
----------------------------------------------------------------

  -------------------------------------------------------------------------------
NOTE:
-------------------------------------------------------------------------------
1) If both @IS_TABLE_VARIABLE and @IS_TEMPORARY_TABLE parameters are set to 1
 then the function will return an empty result set (NULL).

  2) The DDL will always be HEAP.

  3) The column nullability is the only constraint in the DDL

  4) The function will return an empty result set (NULL) if any query profiling
 options are enabled:
   a) STATISTICS PROFILE
   b) STATISTICS XML
   c) SHOWPLAN_ALL
   d) SHOWPLAN_TEXT
   e) SHOWPLAN_XML
   f) "Include Actual Execution Plan" (SSMS)
-------------------------------------------------------------------------------
USAGE:
-------------------------------------------------------------------------------
The function is an alternative and enhancement to SET FMTONLY which has been
deprecated and OPENROWSET which requires enabeling the
"Ad Hoc Distributed Queries" configuration.

  -------------------------------------------------------------------------------
VERSION HISTORY:
-------------------------------------------------------------------------------
1.0 Initial coding
1.1 Added table variable option
1.2 Added temporary table option
1.3 Output changed to NULL for invalid table type options
1.4 Minor changes in output formst
  Added usage examples to the comment section
1.5 Added the option of appending an insert statement to the output.
1.6 Changed the @CREATE_INSERT_DML parameter to TINYINT
  Added the option of appendin ghe statement passed in the @EXEC_SQL
  parameter to the output.
-------------------------------------------------------------------------------
EXAMPLES:
-------------------------------------------------------------------------------
1) Create a permanent table (dbo.MY_NEW_TABLE) based on the output of a dynamic
 query:
-------------------------------------------------------------------------------
DECLARE @TABLE_NAME   NVARCHAR(128) = N'MY_NEW_TABLE';
DECLARE @SCHEMA_NAME   NVARCHAR(128) = N'dbo';
DECLARE @EXEC_SQL    NVARCHAR(MAX) = N'SELECT object_id FROM sys.objects';
DECLARE @PARAMETER_STR  NVARCHAR(MAX) = NULL;
DECLARE @IS_TABLE_VARIABLE BIT     = 0;
DECLARE @IS_TEMPORARY_TABLE BIT     = 0;

  SELECT
  X.TABLE_DDL
FROM dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY( @TABLE_NAME
               ,@SCHEMA_NAME
               ,@EXEC_SQL
               ,@PARAMETER_STR
               ,@IS_TABLE_VARIABLE
               ,@IS_TEMPORARY_TABLE) X

  -------------------------------------------------------------------------------
2) Capture the output of a stored procedure into a temporary table
 (#TBL_SP_WHO) and select from the table;
-------------------------------------------------------------------------------
DECLARE @TABLE_NAME   NVARCHAR(128) = N'TBL_SP_WHO';
DECLARE @SCHEMA_NAME   NVARCHAR(128) = N'dbo';
DECLARE @EXEC_SQL    NVARCHAR(MAX) = N'EXEC sp_who';
DECLARE @PARAMETER_STR  NVARCHAR(MAX) = NULL;
DECLARE @IS_TABLE_VARIABLE BIT     = 0;
DECLARE @IS_TEMPORARY_TABLE BIT     = 1;
DECLARE @EXEC_SP_WHO_TO_TABLE NVARCHAR(MAX) =
(
SELECT
  X.TABLE_DDL
FROM dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY( @TABLE_NAME
               ,@SCHEMA_NAME
               ,@EXEC_SQL
               ,@PARAMETER_STR
               ,@IS_TABLE_VARIABLE
               ,@IS_TEMPORARY_TABLE) X
) + N'
INSERT INTO #' + @TABLE_NAME + N'
' + @EXEC_SQL + N'

  SELECT * FROM  #' + @TABLE_NAME + NCHAR(59);

  EXEC (@EXEC_SP_WHO_TO_TABLE);

  -------------------------------------------------------------------------------
3) Capture the result set of a stored procedure into a table variable (@SPWHO)
 and conditionally select from the table variable.

  DECLARE @TABLE_NAME   NVARCHAR(128) = N'SPWHO';
DECLARE @SCHEMA_NAME   NVARCHAR(128) = N'dbo';
DECLARE @EXEC_SQL    NVARCHAR(MAX) = N'EXEC sp_who';
DECLARE @PARAMETER_STR  NVARCHAR(MAX) = NULL;
DECLARE @IS_TABLE_VARIABLE BIT     = 1;
DECLARE @IS_TEMPORARY_TABLE BIT     = 0;
DECLARE @EXEC_SP_WHO_TO_TABLE NVARCHAR(MAX) =
(
SELECT
  X.TABLE_DDL
FROM dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY( @TABLE_NAME
               ,@SCHEMA_NAME
               ,@EXEC_SQL
               ,@PARAMETER_STR
               ,@IS_TABLE_VARIABLE
               ,@IS_TEMPORARY_TABLE) X
) + N'
INSERT INTO @' + @TABLE_NAME + N'
' + @EXEC_SQL + N'

  SELECT * FROM  @' + @TABLE_NAME + N'
WHERE status = ''runnable'' ' + NCHAR(59);

  EXEC (@EXEC_SP_WHO_TO_TABLE);

  -------------------------------------------------------------------------------
4) Generate HEAP DDL for all tables in a database

  DECLARE @PARAMETER_STR  NVARCHAR(MAX) = NULL;
DECLARE @IS_TABLE_VARIABLE BIT     = 0;
DECLARE @IS_TEMPORARY_TABLE BIT     = 0;
DECLARE @NL      NCHAR(2)   = NCHAR(13) + NCHAR(10);
DECLARE @QT      NCHAR(2)   = NCHAR(45) + NCHAR(45);
SELECT
  CONCAT (
   @QT
   ,QUOTENAME(OBJECT_SCHEMA_NAME(STAB.object_id)) + NCHAR(46) + QUOTENAME(STAB.name)
   ,@NL
   ,X.TABLE_DDL
   ,@NL
   ,@NL
    )
FROM  sys.tables STAB
CROSS APPLY dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY
(  STAB.name
  ,OBJECT_SCHEMA_NAME(STAB.object_id)
  ,N'SELECT * FROM '
   + QUOTENAME(OBJECT_SCHEMA_NAME(STAB.object_id))
   + NCHAR(46)
   + QUOTENAME(STAB.name)
   + NCHAR(59)
  ,@PARAMETER_STR
  ,@IS_TABLE_VARIABLE
  ,@IS_TEMPORARY_TABLE
) X
FOR XML PATH(''),TYPE;
******************************************************************************/
RETURN
SELECT
  CASE
  -- Permanent table
   WHEN @IS_TABLE_VARIABLE = 0 AND @IS_TEMPORARY_TABLE = 0 THEN
    N'CREATE TABLE ' + QUOTENAME(@SCHEMA_NAME)
     + NCHAR(46) + QUOTENAME(@TABLE_NAME)
     + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40)

    -- Temporary table
   WHEN @IS_TABLE_VARIABLE = 0 AND @IS_TEMPORARY_TABLE = 1 THEN
    N'CREATE TABLE ' + NCHAR(35) + @TABLE_NAME
     + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40)

    -- Table variable
   WHEN @IS_TABLE_VARIABLE = 1 AND @IS_TEMPORARY_TABLE = 0 THEN
    N'DECLARE ' + NCHAR(64) + (@TABLE_NAME)
     + N' TABLE ' + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40)

    -- Other options are invalid and the result set is emptied by concatenating
  -- the output with NULL.
   ELSE NULL

    END
+ NCHAR(13) + NCHAR(10) + NCHAR(32) +
(
  SELECT
   STUFF(
      (
       SELECT
        NCHAR(44) + QUOTENAME(R.name) + N' '
        + (R.system_type_name) + N' '
        + CASE
         WHEN R.is_nullable = 0 THEN N'NOT NULL'
         ELSE N'  NULL'
        END
        + NCHAR(13) + NCHAR(10)
       FROM sys.dm_exec_describe_first_result_set(@EXEC_SQL,@PARAMETER_STR,0) R
       WHERE R.is_hidden = 0
       ORDER BY R.column_ordinal ASC
       FOR XML PATH(''),TYPE
      ).value('(./text())[1]','NVARCHAR(MAX)')
     ,1,1,N'')
) + NCHAR(41) + NCHAR(59) +
CASE
  WHEN @CREATE_INSERT_DML > 0 THEN
      CASE
    -- Permanent table
      WHEN @IS_TABLE_VARIABLE = 0 AND @IS_TEMPORARY_TABLE = 0 THEN
       N'INSERT INTO ' + QUOTENAME(@SCHEMA_NAME)
        + NCHAR(46) + QUOTENAME(@TABLE_NAME)
        + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40)

      -- Temporary table
      WHEN @IS_TABLE_VARIABLE = 0 AND @IS_TEMPORARY_TABLE = 1 THEN
       N'INSERT INTO ' + NCHAR(35) + @TABLE_NAME
        + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40)

      -- Table variable
      WHEN @IS_TABLE_VARIABLE = 1 AND @IS_TEMPORARY_TABLE = 0 THEN
       N'INSERT INTO ' + NCHAR(64) + (@TABLE_NAME)
        + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40)

      -- Other options are invalid and the result set is emptied by concatenating
    -- the output with NULL.
      ELSE NULL

      END
   + NCHAR(13) + NCHAR(10) + NCHAR(32) +
   (
    SELECT
      STUFF(
        (
          SELECT
           NCHAR(44) + QUOTENAME(R.name) + N' '
          + NCHAR(13) + NCHAR(10)
          FROM sys.dm_exec_describe_first_result_set(@EXEC_SQL,@PARAMETER_STR,0) R
          WHERE R.is_hidden = 0
          ORDER BY R.column_ordinal ASC
          FOR XML PATH(''),TYPE
        ).value('(./text())[1]','NVARCHAR(MAX)')
       ,1,1,N'')
   ) + NCHAR(41)
  ELSE N''
END

  AS TABLE_DDL
;