Temporary Tables and Dynamic SQL

Matt Pavey

When programming in SQL you sometimes need to create a temporary table inside a stored procedure. That part is straightforward:

SELECT TrackingGroupID,
       Tag
INTO   #temp
FROM   MyTable

You can then use #temp through the rest of the procedure and drop it when you are done:

-- testing
SELECT COUNT(*) FROM #temp

-- drop temp table
DROP TABLE #temp

But if you also need dynamic SQL, you will run into scope issues. In my case I needed to apply a dynamic filter to a query and store the results in a temporary table so I could do further manipulation, analysis, and grouping on the filtered data.

Normally I prefer table variables for this:

DECLARE @Temp TABLE
(
    TrackingGroupID INT,
    Tag             VARCHAR(50) NOT NULL DEFAULT ''
)

In this case I could not get a table variable to work, so I used a #temp table instead. My first attempt looked like this:

DECLARE @SQL VARCHAR(8000)

SET @SQL = 'SELECT TrackingGroupID,
                   Tag
            INTO   #temp
            FROM   MyTable
            WHERE  1=1 ' + @Where + '
            GROUP BY TrackingGroupID, Tag'

EXEC(@SQL)

-- do further manipulation or analysis on #temp
-- ...

DROP TABLE #temp

Which gave me this:

Msg 208, Level 16, State 0, Line 18
Invalid object name '#temp'.

I knew it was a scoping problem but was not sure how to work around it. The explanation is that EXEC and sp_executesql run dynamic SQL in a new child scope, and any objects created inside that scope are dropped as soon as it closes. The temp table was being created and destroyed inside the dynamic statement, so by the time the outer procedure went looking for it, it was gone.

The fix is to create the table in the outer scope first, then have the dynamic SQL insert into it rather than create it:

-- create temp table in the outer scope
CREATE TABLE #temp
(
    TrackingGroupID INT,
    Tag             VARCHAR(50) NOT NULL DEFAULT ''
)

DECLARE @SQL VARCHAR(8000)

SET @SQL = 'INSERT INTO #temp
            (TrackingGroupID, Tag)
            SELECT TrackingGroupID,
                   Tag
            FROM   MyTable
            WHERE  1=1 ' + @Where + '
            GROUP BY TrackingGroupID, Tag'

EXEC(@SQL)

-- do further manipulation or analysis on #temp
-- ...

DROP TABLE #temp

Now the table exists for the whole scope of the stored procedure, and the dynamic statement is only writing to it.

A global temp table, ##temp, would likely work here as well, though I did not go down that road.