Issues When Using Temporary Tables in Nested Stored Procedures

SQL Server offers a lot of flexibility when working with objects like stored procedures and temporary tables.  One of the capabilities is to allow for stored procedures to call other stored procedures.  This is called “nesting” one stored procedure within the context of the calling stored procedure.  In addition, you can instantiate a temporary table from within the parent procedure that can be utilized within the context of the child procedure.

But wait! I’m not done yet!

You can also instantiate a temporary table with the same name as the parent temporary table within the child procedure.  But there is a catch!

From Microsoft Documentation:

Nested stored procedures can also create temporary tables with the same name as a temporary table that was created by the stored procedure that called it. However, for modifications to resolve to the table that was created in the nested procedure, the table must have the same structure, with the same column names, as the table created in the calling procedure. 

In other words, the name and the schema of the child temporary table must be identical to the name and schema of the parent temporary table.  Otherwise, mass confusion reigns supreme and you could get errors when attempting to do any data modifications in the child temporary table.

Let’s look at some code so that we can see the behavior.

Examining the behavior

First, here’s the code to show the example:

USE Test
GO

CREATE OR ALTER PROCEDURE dbo.parent
AS
BEGIN
       CREATE TABLE #test (name sysname)
       INSERT #test (name) VALUES ('Joey')

       EXEC dbo.child

       SELECT * FROM #test
END
GO

CREATE OR ALTER PROCEDURE dbo.child
AS
BEGIN
       CREATE TABLE #test (fname sysname)
       INSERT #test (fname) VALUES ('John')

       SELECT * FROM #test
END
GO

-- Execute the parent procedure
EXEC dbo.parent

Now, let’s step through it so that we have a clear understanding of what it is doing.

  1. Create a parent procedure that creates a temporary table called #test with “name” as the column name.
    1. Insert a row into the parent temporary table
    2. Call a child stored procedure
    3. Select from the parent temporary table
  2. Create a child stored procedure that creates a temporary table also called #test with “fname” as the column name. Note that this column name is different from the parent temporary table.
    1. Insert a row into the child temporary table
    2. Select from the child temporary table

Below is the error that is returned when executing the code block.

SSMS Screen Shot showing an error

The error above is stating that the “fname” column does not exist within the temporary table but we can see from the code block that is most definitely is.  This means that SQL Server is attempting to update the parent temporary table and NOT the child temporary table as one might expect.

Summary

When working with nested procedures as well as nested temporary tables, make sure to either have the identical name and schema or make sure to use a different naming convention.  By doing so you help to eliminate any issues of the SQL Server not resolving to the appropriate temporary table.

 

© 2021, John Morehouse. All rights reserved.

4 Replies to “Issues When Using Temporary Tables in Nested Stored Procedures”

  1. Cheers for the post, it’s not behaviour I was aware of, and as I’m looking at using some similar functionality at the moment I had a bit of a play and found a few other things.

    If you ‘correct’ the child proc so it creates a #test table with a column called name, everything works ok but SQL Server is working with 2 temp tables, so you return ‘John’ from the child proc and ‘Joey’ from the parent proc.
    If you just get rid of the create in the child proc and insert direct to the #test from the parent proc SQL Server is obviously only working with the one temp table so you get ‘Joey’ ‘John’ in your result set from both procs.
    If you change the child proc to insert into #test(name) it still fails.
    If you change the child proc to insert into #test with no column list, it works as it did in point 1 (i.e. SQL Server creates 2 distinct temp tables and inserts successfully into both).
    This stops working if you create different numbers of columns in the two temp tables (I added a column called name to the child temp table)
    But if the other columns are NULLable you can still insert into common columns (I can still insert into #temp(name) in child if I make fName NULLable)
    And if you add an equal number of columns to both tables you can still insert provided you don’t specify the target columns
    If you do this, the only way you can select fname (or any column name only present in one table) is with SELECT *, SELECT fname will throw an error.

    So, it looks like SQL Server will create a second temp table with the same name, but gets confused when you try something that wouldn’t work on both tables.

    That ended up being a longer reply than I thought it would be, but every time I added something to the list I thought of something else to try.

  2. It is a clear bug that SQL Server allows to redefine a temp table in a scope where it is already present. So just stay away an do not define anew the temp table in child procedure.

  3. I’m not sure that it’s a bug per say since the docs clearly state this is the behavior, but I’d agree with your premise. I would stay away from naming any children temp tables the same as what’s defined in the parent. This would just remove any question whether or not that’s the issue. Thanks for stopping by!

Hey you! Leave me a comment and start a discussion!

This site uses Akismet to reduce spam. Learn how your comment data is processed.