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.
- Create a parent procedure that creates a temporary table called #test with “name” as the column name.
- Insert a row into the parent temporary table
- Call a child stored procedure
- Select from the parent temporary table
- 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.
- Insert a row into the child temporary table
- Select from the child temporary table
Below is the error that is returned when executing the code block.
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.
The post Issues When Using Temporary Tables in Nested Stored Procedures first appeared on John Morehouse.
One Response
“This means that SQL Server is attempting to update the parent temporary table and NOT the child temporary table as one might expect.”
This is not necessarily true – I think it’s more of an issue of TempDB’s temp table caching strategy conflicting with nested proc temp table scoping. Paul White has a few articles related to this.
If I change the column name to “name” in the dbo.child proc so the schemas are identical, running dbo.parent outputs “John” in one result (from dbo.child’s #test table) and only “Joey” in the another result (from dbo.parent’s #test table).
However, if I then comment out the Create Table in dbo.child and run dbo.parent, both results have 2 rows (Joey and John), as child was able to use #test from parent without schema or scoping issues.
From SQL BOL for Create Table:
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.