This was an error I encounted the other week at work and I thought it worth sharing as it’s a bit of a gotcha. You normally expect that temp tables are fine to share names across sessions since they are designed to be temporary. However, there are a few times when conflicts can occur. See this post on MSSQL Tips for more details. Basically, if we don’t do any of the things that disallow caching, then trying to re-use a temp table name in a “inner” called stored procedure will cause a conflict. Those three things are:

  • Creating a named constraint, like a named primary key
  • Using a DDL statement on the table such as CREATE INDEX
  • Using dynamic SQL to create the table

Here is my minimum example. Since we use temporary stored procedures and temp tables this code can be run on any database in SQL.

/*We won't need to drop the procedures as they are temporary. You
can use # to create a temporary stored procedure like a temporary
table*/

CREATE OR ALTER PROC #TempTableCacheTestInnerProc
AS
BEGIN 
	/*This will be the inner proc that creates a temp table and
	tries to select a column that doesn't exist in the outer
	version*/   
	SELECT	1 AS A,           
			2 AS B    
	INTO	#CACHED_TEMP_TABLE  

	/*Clearly this *should* work as columns A and B were created
	just above*/
	SELECT  A,
			B
	FROM    #CACHED_TEMP_TABLE
	
END
GO

CREATE OR ALTER PROC #TempTableCacheTestOuterProc
AS
BEGIN 
	/*This is the outer proc that creates the temp table
	lacking a B columns. Then it calls the inner proc.
	The same behaviour occurs using the syntax SELECT INTO*/   

	CREATE TABLE #CACHED_TEMP_TABLE (
		A	INT NOT NULL
	)

	INSERT INTO	#CACHED_TEMP_TABLE ( A )   
	VALUES ( 4 )

	SELECT	T.A    
	FROM    #CACHED_TEMP_TABLE AS T

	/*Executing this proc without dropping the table
	#CACHED_TEMP_TABLE causes a conflict*/
	EXEC	#TempTableCacheTestInnerProc
	
END
GO

/*Executing the outer procedire will give the
error "Invalid column name 'B'."*/
EXEC #TempTableCacheTestOuterProc

A good one to keep in mind. Try to make your temporary table names a bit more descriptive. I’ve seen far too many instances of #TEMP or #RESULTS, and that’s just asking for trouble. It makes it harder for the next person to work on the code, too.