Dynamic query results into a temp table or table variable

Question

I have a stored procedure that uses sp_executesql to generate a result set, the number of columns in the result can vary but will be in the form of Col1 Col2 Col3 etc.

I need to get the result into a temp table or table variable so I can work with it. The problem is I need to define the columns of the temp table, which I cant do dynamically using sp_executesql as the scope of the temp table is lost after the command is executed.

I have toyed with the idea of using Global Temp tables, as the scope allows it to be created dynamically, however, there is a very good chance the Global Temps would get updated by the concurrent executions of this process.

Any ideas?

Answer 1

I have found a solution that works for me with the help of @SQLMenace in this post T-SQL Dynamic SQL and Temp Tables

In short, I need to create a #temp table in normal SQL first, then I can alter the structure using further dynamic SQL statements. In this example @colcount is set to 6. This will be determined by another stored proc when I implement this.

IF object_id('tempdb..#myTemp') IS NOT NULL
DROP TABLE #myTemp

CREATE TABLE #myTemp (id int IDENTITY(1,1) )
DECLARE @cmd nvarchar(max)
DECLARE @colcount int
SET @colcount = 6
DECLARE @counter int
SET @counter = 0
WHILE @counter < @colcount
    BEGIN
      SET @counter = @counter + 1
      SET @cmd = 'ALTER TABLE #myTemp  ADD col' + CAST(@counter AS varchar(4)) + ' NVARCHAR(MAX)'
      EXEC(@cmd)
    END

INSERT INTO #myTemp 
EXEC myProc @param1, @param2, @param3

SELECT * FROM #myTemp