Temp table scope

A customer approached me with a strange problem.  They were using one of the system stored procedures, sp_help_log_shipping_monitor, to make sure that log shipping was up to date before running a report against the secondary database.  The system stored procedure returns a lot more data than they needed to see, and they wanted to filter the results based on a database name. In fact, they only really wanted one piece of information from the system stored proc, the status column.

They came up with a great solution. They wrote their own user stored procedure that accepted the database name as a parameter.  Inside the user stored procedure, a temp table was populated with the results of the system stored procedure. Then the results were filtered and the status was returned as a return value to the calling application.

Theoretically, it should have worked. The problem was that the new custom stored procedure would often returned an error message originating from the system stored procedure about a missing column, “ts”. Running the system stored
procedure by itself always worked great. In fact, if they ran the system stored procedure before running the user stored procedure, the user stored procedure would work as expected.

Before attempting to help them, I took a look at the system stored procedure’s definition. I saw that the missing column was used for sorting the data returned by the proc, but not included in the columns returned. I knew that modifying a system stored proc is not a good idea.

When my customer showed me their custom user stored procedure definition, the source of the problem was obvious. The temp table in the user stored procedure was named exactly the same as the temp table in the system stored procedure, #log_shipping_monitor.  Somehow, the temp table in the custom user stored proc was being used inside the system stored proc instead of the one created there.

The fix was easy: just change the name of the temp table in the user stored procedure.  Doing this solved the problem, and their solution has been working flawlessly since then.  Another solution would have been to change the temp table in the user stored procedure into a table variable. Either way would have worked.

A local temporary table is only in scope within the connection in which it was created. (Note: A local temporary table is one in which the name begins with #. Global temporary tables can be seen by other connections, and the name begins with ##.) A local temporary table that is created within a stored procedure is automatically dropped once the stored
procedure execution completes. 

In my customer’s situation, when the system stored procedure was called within their user stored procedure, the system stored procedure could see both temporary tables.  Here is an example:

IF OBJECT_ID('usp_OuterProc') IS NOT NULL DROP PROC usp_OuterProc;
IF OBJECT_ID('usp_InnerProc') IS NOT NULL DROP PROC usp_InnerProc;

GO 

CREATE PROC usp_InnerProc AS 
      SET NOCOUNT ON;

      CREATE TABLE #tables(name SYSNAME,  [object_id] INT, create_date datetime DEFAULT GETDATE());

      INSERT INTO #tables(name, [object_id]) 
      SELECT name, object_id
      FROM sys.tables; 

      SELECT name, [object_id]
      FROM #tables
      ORDER BY create_date;
GO

 CREATE PROC usp_OuterProc AS 
      SET NOCOUNT ON;

      CREATE TABLE #tables(name SYSNAME,  object_id INT);

      INSERT INTO #tables EXEC usp_InnerProc
      SELECT name, [object_id]
      FROM #tables;

When I run usp_OuterProc, I get an error about missing column create_date “Msg 207, Level 16, State 1, Procedure usp_InnerProc, Line 16 Invalid column name 'create_date'.”.

If I run usp_InnerProc right before running usp_OuterProc, usp_OuterProc will work. Usp_InnerProc is seeing both temporary tables when it is called by usp_OuterProc. Let’s try another example.

IF OBJECT_ID('usp_OuterProc') IS NOT NULL DROP PROC usp_OuterProc;
IF OBJECT_ID('usp_InnerProc') IS NOT NULL DROP PROC usp_InnerProc;

GO

CREATE PROC usp_InnerProc AS

SET NOCOUNT ON;
SELECT * FROM #myNewTable;

GO 

CREATE PROC usp_OuterProc AS

      CREATE TABLE #myNewTable(name SYSNAME, ID INT);
      INSERT INTO #myNewTable (name, ID)
      SELECT name, [object_id]
      FROM sys.tables;

EXEC usp_InnerProc;

GO

In this example, running usp_InnerProc fails with this message: Msg 208, Level 16, State 0, Procedure usp_InnerProc, Line 4 Invalid object name '#myNewTable'.   When I run usp_OuterProc, I get the expected results.  The temporary table must exist before usp_InnerProc is called.

The moral of the story is that it is a good idea to create your own stored procedures to do something that SQL Server doesn’t do or doesn’t do exactly as you need it to. Just make sure that any temporary tables you create have unique names.