Getting access to a dynamic table from dynamic sql

Question

Good day StackOverflow

The table that I create from my dynamic sql can have any number of columns as it is a pivot table.

-- Pivot the table so we get the UW as columns rather than rows
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = '
SELECT *
FROM #PreProcessed
PIVOT (SUM(Quotes)
        FOR [UW] IN (' + @UWColumns + ')
        ) AS bob'

I run this code to run my dynamic sql.

EXEC sp_executesql @SQL,
                  N'@UWColumns nvarchar(MAX)',
                  @UWColumns

My question is, how do I store the resulting table? Especially when I don't know how many columns it will have or even what the columns will be called?

I tried the code below but it doesn't work

   INSERT INTO #Temp
   EXEC sp_executesql @SQL,
                  N'@UWColumns nvarchar(MAX)',
                  @UWColumns

Thanks everyone

You'll probably need to create a real table. A temp table created outside of `sp_exectutesql` will have scope within in. But anything transient (temp tables, variables, etc) created within it will de-allocate when the scope returns to the calling code. So, dynamic SQL to create a table, as well as dynamic SQL to populate it. Or, re-factor your code to work with the source (normalised) data. — MatBailie, Dec 09 '13 at 14:57

score 0 · Answer 1 · answered Dec 09 '13 at 14:49

0

SQL Server uses SELECT * INTO ...., as opposed to the CREATE TABLE AS syntax. So you'll need to modify your dynamic sql to:

 SELECT * INTO <YOUR TABLE>
    FROM #PreProcessed
    PIVOT (SUM(Quotes)
            FOR [UW] IN (' + @UWColumns + ')
            ) AS bob'

answered Dec 09 '13 at 14:49

Andrew

8,445
3
28
46

Hi thanks for the help but I get this error: Msg 208, Level 16, State 0, Line 35 Invalid object name '#temp'. – Bobby Dec 09 '13 at 14:52
Oh, didn't catch the table variable in there. I'm not sure how you would do that, since you have to declare the variable. Why are you trying to save the results in a temp table? – Andrew Dec 09 '13 at 15:00
I am writing quite a complex report and I need to add some more information to the data I am producing. Unfortunately this all will be wrapped into a sp that needs to be run when ever. My colleague suggested using a second table and joining on my date column – Bobby Dec 09 '13 at 15:03
So you need it only for the life of your stored procedure? Maybe you could use it as a CTE? `;with – Andrew Dec 09 '13 at 15:09

score 0 · Answer 2 · answered Dec 09 '13 at 16:40

The only way I could find around this problem was to do all of my calculations in the dynamic sql. Which meant I had to work on two tables.

-- Pivot the table so we get the UW as columns rather than rows
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = '
SELECT * INTO #temp
FROM #PreProcessed
PIVOT (SUM(Quotes)
        FOR [UW] IN (' + @UWColumns + ')
        ) AS bob

SELECT DISTINCT t1.Date, d.Declines AS ''Declines'' , '+@UWColumns+'
FROM #temp AS t1 LEFT OUTER JOIN
#Declines AS d ON t1.DATE = d.DATE
'

PRINT @SQL

EXEC(@SQL)

Getting access to a dynamic table from dynamic sql

2 Answers2