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?
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
IS there any reason you can't do something like:
SELECT *
INTO #MyTempTable
FROM MyResultSet
SELECT INTO doesn't require an explicit field list.
You can use global temp tables whose names are 'uniquified' by the SPID of the creating process. This can allow you to avoid stomping on other global temp tables created by other connections.
Just make sure to clean them up when you're done... :)
