Temporary Tables and Dynamic SQL

When programming in SQL sometimes you need to generate temporary tables inside a stored procedure. That's pretty straight forward with code like this:

 

SELECT     TrackingGroupID,
               Tag

INTO        #temp
FROM       MyTable

 

And you can access #temp in the rest of your stored procedure and drop the temp table when complete.

 

-- testing
SELECT COUNT(*) FROM #temp

 

-- drop temp table

DROP TABLE #temp

 

But if you ever have a situation where you also need to use dynamic SQL you will likely have scope issues. In my particular case I ran into a situation where I needed to apply a dynamic filter to a query and store that data in a temporary table so I could access it after the filter was applied and do further manipulation and analysis and grouping with the data.

 

Note: Typically when I work with temp tables I try to use tables-variables like so:

 

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

 

However, in this case I was not able to get a table-variable to work. So I ended up using a #temp table instead.

 

My first attempt looked something like this:

 

-- declare variables
DECLARE @SQL VARCHAR(8000)

 

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

-- apply filter
EXEC(@SQL)

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

-- drop temp table
DROP TABLE #temp

 

But I received this error:

 

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

 

I knew it was a scoping issue, but I wasn't sure how to work around it exactly. Researching on Google I came across this article:

 

http://blog.shkedy.com/2007/01/temporary-tables-and-dynamic-sql.html

 

"The problem here is the scope of the session. When we execute dynamic sql via EXEC or sp_executesql a new scope is created for a child session. Any objects created in that session are dropped as soon as the session is closed."

 

So I modified my code like so:

 

-- create temp table

CREATE TABLE #temp
(
     TrackingGroupID  INT,
     Tag                    VARCHAR(50) NOT NULL DEFAULT ''
)

 

-- declare variables
DECLARE @SQL VARCHAR(8000)

 

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

 

-- apply filter
EXEC(@SQL)

 

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

 

-- drop temp table
DROP TABLE #temp

 

Now I had access to the #temp table throughout the scope of the stored procedure!

 

I've been told that ##temp might have solved it also since it's global; however, I'm not as familiar with ## so I didn't go down that road.