Thursday, May 1, 2008

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:
 
 
"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.

0 comments: