Dynamic SQL results into temp table in SQL Stored procedure

Question

The code is as follows:

ALTER PROCEDURE dbo.pdpd_DynamicCall 
@SQLString varchar(4096) = null

AS

Begin

    create TABLE #T1 ( column_1 varchar(10) , column_2 varchar(100) )

    insert into #T1 
        execute ('execute ' + @SQLString )

    select * from #T1 

End

The problem is that I want to call different procedures that can give back different columns. Therefore I would have to define the table #T1 generically. But I don't know how.

Can anyone help me on this problem?

Sound like you should stop trying to use stored procedures and use plain old parametered sql scripts. — jason saldo, Mar 19 '09 at 16:19

score 46 · Accepted Answer · edited May 24 '11 at 18:44

46

Try:

SELECT into #T1 execute ('execute ' + @SQLString )

And this smells real bad like an sql injection vulnerability.

correction (per @CarpeDiem's comment):

INSERT into #T1 execute ('execute ' + @SQLString )

also, omit the 'execute' if the sql string is something other than a procedure

edited May 24 '11 at 18:44

Brad

15,361
6
36
57

answered Mar 19 '09 at 12:56

Joel Coehoorn

399,467
113
570
794

Tough accepted answer, I cannot make this work! First it complains that there is no * after SELECT. And when I put it, it complains that there is no table to select from.. SELECT * INTO #tmp_input EXECUTE('SELECT 1 AS test') ; >>> SQL Server Database Error: Must specify table to select from. On SQL Server 2005! – ercan Dec 01 '09 at 15:02
1

I have a same kind of Issue, What if i do not know the no. of columns @SQLString returns? what to do then. – Srivastav Nov 20 '14 at 07:36
2

#T1 must be created before the answer above will work. – Roger Dec 17 '16 at 17:24
Please update the accepted answer to ##T1 (instead of #T1) as that is what works. That was provided by @Jesse downthread. – John Joseph Jul 25 '17 at 04:11
1

Is this working for anyone without creating global temp table? I am getting `Invalid object name '#T1'.` and you cannot create #T1 in advance because, as OP mentioned, you don't know the columns – LP13 May 22 '20 at 04:41

score 38 · Answer 2 · answered Jun 16 '09 at 18:50

38

You can define a table dynamically just as you are inserting into it dynamically, but the problem is with the scope of temp tables. For example, this code:

DECLARE @sql varchar(max)
SET @sql = 'CREATE TABLE #T1 (Col1 varchar(20))'
EXEC(@sql)
INSERT INTO #T1 (Col1) VALUES ('This will not work.')
SELECT * FROM #T1

will return with the error "Invalid object name '#T1'." This is because the temp table #T1 is created at a "lower level" than the block of executing code. In order to fix, use a global temp table:

DECLARE @sql varchar(max)
SET @sql = 'CREATE TABLE ##T1 (Col1 varchar(20))'
EXEC(@sql)
INSERT INTO ##T1 (Col1) VALUES ('This will work.')
SELECT * FROM ##T1

Hope this helps, Jesse

answered Jun 16 '09 at 18:50

Jesse

449
4
3

4

This is it! Now I can create the table inside my stored procedure, with the name passed via an argument! If I could give 10 votes! – Tomasz Gandor Nov 03 '15 at 11:47
2

I have finally contacted the SQL 'spirit world' and summoned a demonic dynamic table created there to come back out of it thanks to you! :) – Eddy Jawed Dec 09 '19 at 19:08
this is how actually works, the other answer is wrong, thank you – Ronaldo Cano Jun 04 '21 at 22:25
But the question says that the list of columns is dynamic, so we can't define the temp table before hand – Siddharth Dinesh Apr 19 '23 at 14:26

score 25 · Answer 3 · answered Nov 15 '10 at 16:03

25

Be careful of a global temp table solution as this may fail if two users use the same routine at the same time as a global temp table can be seen by all users...

answered Nov 15 '10 at 16:03

Runonthespot

875
9
17

score 12 · Answer 4 · answered Mar 15 '13 at 17:26

create a global temp table with a GUID in the name dynamically. Then you can work with it in your code, via dyn sql, without worry that another process calling same sproc will use it. This is useful when you dont know what to expect from the underlying selected table each time it runs so you cannot created a temp table explicitly beforehand. ie - you need to use SELECT * INTO syntax

DECLARE @TmpGlobalTable varchar(255) = 'SomeText_' + convert(varchar(36),NEWID())

-- select @TmpGlobalTable 

-- build query
    SET @Sql = 
        'SELECT * INTO [##' + @TmpGlobalTable + '] FROM SomeTable'
EXEC (@Sql)
EXEC ('SELECT * FROM [##' + @TmpGlobalTable + '] ')
EXEC ('DROP TABLE [##' + @TmpGlobalTable + ']')
PRINT 'Dropped Table ' + @TmpGlobalTable

Good solution. Note the square brackets around the table name: NEWID converted to varchar contains "-" which are invalid in object names otherwise. — SebTHU, Nov 14 '17 at 13:57

score 8 · Answer 5 · answered Jun 14 '11 at 22:29

8

INSERT INTO #TempTable
EXEC(@SelectStatement)

answered Jun 14 '11 at 22:29

MTS

1,845
2
17
18

score 1 · Answer 6 · answered Mar 18 '21 at 11:03

Try Below code for creating temp table dynamically from Stored Procedure Output using T-SQL

declare @ExecutionName varchar(1000) = 'exec [spname] param1,param2 '
declare @sqlStr varchar(max) = ''

   declare @tempTableDef nvarchar(max) =   
  (  
  SELECT distinct   
   STUFF(  
    (  
     SELECT ','+a.[name]+' '+[system_type_name]  
  +'  
   ' AS [text()]  
     FROM sys.dm_exec_describe_first_result_set  (@ExecutionName, null, 0) a  
     ORDER BY a.column_ordinal  
     FOR XML PATH ('')  
    ), 1, 1, '') tempTableDef   

  FROM sys.dm_exec_describe_first_result_set  (@ExecutionName, null, 0) b  
  )  

  IF ISNULL(@tempTableDef ,'') = '' RAISERROR( 'Invalid SP Configuration. At least one column is required in Select list of SP output.',16,1) ;    
                                      
  set @tempTableDef='CREATE TABLE #ResultDef   
  (  
  ' + REPLACE(@tempTableDef,'&#x0D;','') +'  
  )  

  INSERT INTO #ResultDef   
  ' + @ExecutionName    
          
  Select  @sqlStr  = @tempTableDef +' Select * from  #ResultDef '   
exec(@sqlStr)

score 0 · Answer 7 · edited Nov 22 '16 at 08:38

CREATE PROCEDURE dbo.pdpd_DynamicCall 
AS
DECLARE @SQLString_2 NVARCHAR(4000)
SET NOCOUNT ON
Begin
    --- Create global temp table
    CREATE TABLE ##T1 ( column_1 varchar(10) , column_2 varchar(100) )

    SELECT @SQLString_2 = 'INSERT INTO ##T1( column_1, column_2) SELECT column_1 = "123", column_2 = "MUHAMMAD IMRON"'
    SELECT @SQLString_2 = REPLACE(@SQLString_2, '"', '''')

    EXEC SP_EXECUTESQL @SQLString_2

    --- Test Display records
    SELECT * FROM ##T1

    --- Drop global temp table 
    IF OBJECT_ID('tempdb..##T1','u') IS NOT NULL
    DROP TABLE ##T1
End

score 0 · Answer 8 · edited Oct 29 '12 at 02:35

0

DECLARE @EmpGroup INT =3 ,
        @IsActive BIT=1

DECLARE @tblEmpMaster AS TABLE
        (EmpCode VARCHAR(20),EmpName VARCHAR(50),EmpAddress VARCHAR(500))

INSERT INTO @tblEmpMaster EXECUTE SPGetEmpList @EmpGroup,@IsActive

SELECT * FROM @tblEmpMaster

edited Oct 29 '12 at 02:35

Vikdor

23,934
10
61
84

answered Mar 31 '12 at 11:01

URVISH SUTHAR

1
1

score -1 · Answer 9 · answered Mar 19 '09 at 12:40

-1

Not sure if I understand well, but maybe you could form the CREATE statement inside a string, then execute that String? That way you could add as many columns as you want.

answered Mar 19 '09 at 12:40

Seb

24,920
5
67
85

but, i don't know columns, it is dynamic. – Dhanapal Mar 19 '09 at 12:45

Dynamic SQL results into temp table in SQL Stored procedure

9 Answers9

Linked