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.
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.