Temp Table Collision Between Sprocs

I recently debugged a report in which one sproc called another (let's call them OuterSproc and InnerSproc), and the resulting error was a complete mystery. When I executed InnerSproc on it's own, with the same param values provided by OuterSproc, the results were fine. After scratching my head for awhile, I decided to just keep debugging by executing lines from OuterSproc, and that's when I noticed that the two sprocs shared some temp table names. We tend to think of temp tables (as in "#temp"), as being local to the scope that created them, but that's not the whole story. Temp tables are scoped in with any code that is called from that scope also. So when OuterSproc creates data in #temp, then calls InnerSproc, InnerSproc can see and modify #temp. The problem is that InnerSproc creates its own #temp table, not thinking that it already exists. As an example of how this can be a problem, take a look at this code sample:

USE TRPTools
GO
DROP PROCEDURE InnerSproc
GO
CREATE PROCEDURE InnerSproc
AS
BEGIN
SELECT * FROM #temp
CREATE TABLE #temp (Sproc_id int, sproc_name varchar(20), when_done datetime)
INSERT #temp (Sproc_id, sproc_name) VALUES (OBJECT_ID('tempdb..#temp'), 'InnerSproc')
SELECT OBJECT_ID('tempdb..#temp')
SELECT * FROM #temp
END
GO
 
 
DROP PROCEDURE OuterSproc
GO
CREATE PROCEDURE OuterSproc
AS
BEGIN
CREATE TABLE #temp (Sproc_id int, sproc_name varchar(20), who_did varchar(20))
INSERT #temp (Sproc_id, sproc_name) VALUES (OBJECT_ID('tempdb..#temp'), 'OuterSproc')
SELECT OBJECT_ID('tempdb..#temp')
EXEC InnerSproc
SELECT * FROM #temp
END
GO
EXEC OuterSproc



Notice in sproc InnerSproc that before it creates #temp, it selects from it. This is perfectly acceptable code, because of the downstream scope of temp tables. What is weird is that the same line of code, SELECT * FROM #temp, at the beginning and end of the sproc, produces different results! The first time, it pulls from the OuterSproc version of #temp, and the second, from the InnerSproc version. It's as if the CREATE TABLE #temp statement in InnerSproc creates a version of #temp that overrides the original definition, but only within the scope of InnerSproc. Preliminary research finds no explanation of this behavior.

Takeaways:

• Use temp table names that are non-generic, that are specific to your project/code/data
• Consider using table variables instead, as these are scoped only to the code that creates them.