Analysis on the strange image of using the temporary table with the same name in the nested stored procedure of SQL Server

Time:2021-8-22

Can a local temporary table with the same name exist in the nested stored procedure, outer stored procedure and inner stored procedure (nested called stored procedure) of SQL Server? If so, are there any problems or restrictions? In the nested stored procedure, is it called a temporary table of the external stored procedure or a temporary table defined by itself? Like variables in high-level languages, does the local temporary table have a scope?

Note: it can also be called parent stored procedure and child stored procedure, outer stored procedure and inner stored procedure. These are just different names or names. We use both outer and inner stored procedures. The following articles will not be described.

Let’s take a look at an example. As shown below, we construct a simple example.


IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.PRC_TEST') AND OBJECTPROPERTY(object_id, 'IsProcedure') =1)
BEGIN
  DROP PROCEDURE dbo.PRC_TEST
END
GO
CREATE PROC dbo.PRC_TEST
AS
BEGIN
 
  CREATE TABLE #tmp_test(id INT);
 
  INSERT INTO #tmp_test
  SELECT 1;
 
  SELECT * FROM #tmp_test;
 
  EXEC PRC_SUB_TEST
 
  SELECT * FROM #tmp_test
  
 
END
GO
 
IF EXISTS(SELECT 1 FROM sys.objects WHERE object_id= OBJECT_ID(N'dbo.PRC_SUB_TEST' ) AND OBJECTPROPERTY(object_id, 'IsProcedure')=1)
BEGIN
  DROP PROCEDURE dbo.PRC_SUB_TEST;
END
GO
 
CREATE PROCEDURE dbo.PRC_SUB_TEST
AS
BEGIN
  
  CREATE TABLE #tmp_test(name VARCHAR(128));
 
  INSERT INTO #tmp_test
  SELECT name FROM sys.objects
 
  SELECT * FROM #tmp_test;
END
GO
 
EXEC PRC_TEST;

The simple test seemed normal and found no problems. If you come to a conclusion at this time, it’s too early! For example, if you see a swan is white, if you make a conclusion: “all swans are white”, in fact, there are black swans in the world, but you haven’t seen them! As shown below, let’s modify the stored procedure dbo. PRC_ SUB_ Test, replace * with field name name, as follows:


IF EXISTS(SELECT 1 FROM sys.objects WHERE object_id= OBJECT_ID(N'dbo.PRC_SUB_TEST' ) AND OBJECTPROPERTY(object_id, 'IsProcedure')=1)
BEGIN
  DROP PROCEDURE dbo.PRC_SUB_TEST;
END
GO
 
CREATE PROCEDURE dbo.PRC_SUB_TEST
AS
BEGIN
  
  CREATE TABLE #tmp_test(name VARCHAR(128));
 
  INSERT INTO #tmp_test
  SELECT name FROM sys.objects
 
  SELECT name FROM #tmp_test;
END
GO

Then repeat the above test, as shown below, and execute the stored procedure dbo. PRC_ Test will report an error: “invalid column name ‘name’.”

At this point, I only need to execute the stored procedure dbo.prc once_ SUB_ Test, and then execute the stored procedure dbo.prc_ Test will not report an error. Moreover, the stored procedure is executed once, and then dbo. PRC is executed in the current session or any other session_ Test won’t report wrong. Is it very confusing or confusing.


EXEC dbo.PRC_SUB_TEST;
 
EXEC PRC_TEST;

If you want to reproduce this phenomenon again, you can only reproduce this phenomenon by following SQL or deleting / rebuilding stored procedures. It seems like a ghost phenomenon.


DBCC FREEPROCCACHE

This phenomenon is described in the official document (see the link address of resources for details):

A local temporary table created within a stored procedure or trigger can have the same name as a temporary table that was created before the stored procedure or trigger is called. However, if a query references a temporary table and two temporary tables with the same name exist at that time, it is not defined which table the query is resolved against. 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. This is shown in the following example.

The name of the local temporary table created in the stored procedure or trigger can be the same as the name of the temporary table created before calling the stored procedure or trigger. However, if the query references a temporary table and there are two temporary tables with the same name at the same time, it does not define which table to resolve the query for. A nested stored procedure can also create a temporary table with the same name as the temporary table created by the stored procedure calling it. However, in order to modify it to resolve to a table created in a nested procedure, the table must have the same structure and column names as the table created by the calling procedure. The following example illustrates this.


CREATE PROCEDURE dbo.Test2
AS
  CREATE TABLE #t(x INT PRIMARY KEY);
  INSERT INTO #t VALUES (2);
  SELECT Test2Col = x FROM #t;
GO
 
CREATE PROCEDURE dbo.Test1
AS
  CREATE TABLE #t(x INT PRIMARY KEY);
  INSERT INTO #t VALUES (1);
  SELECT Test1Col = x FROM #t;
EXEC Test2;
GO
 
CREATE TABLE #t(x INT PRIMARY KEY);
INSERT INTO #t VALUES (99);
GO
 
EXEC Test1;
GO

In the official document, “if there are two temporary tables with the same name at the same time, it does not define which table to parse the query” is still a little confusing. Here is a brief explanation. In the nested call of stored procedures, local temporary tables with the same name are allowed in the outer and inner stored procedures. However, if they need to be modified or parsed in the memory process, it is easy to understand, such as DDL operations such as adding indexes and fields; As for parsing, query the temporary table. If the field name is specified in SQL, it needs to be resolved. At this time, the temporary table must have the same table structure, otherwise an error will be reported. The official document is a sentence that tells you no, but the specific reason is not said. So we might as well speculate whether two local temporary tables are created in the nested call of the stored procedure? Is it possible to actually create only one local temporary table? What about the reuse of local temporary tables? Let’s simply verify, as shown below, that two local temporary tables are actually created. There is no temporary table reuse.


SELECT * 
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Temp Tables Creation Rate%';
 
EXEC PRC_TEST;
 
SELECT * 
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Temp Tables Creation Rate%';

Of course, you can use the following SQL to verify, which is consistent with the verification results above.


IF EXISTS(SELECT 1 FROM sys.objects WHERE object_id= OBJECT_ID(N'dbo.PRC_SUB_TEST' ) AND OBJECTPROPERTY(object_id, 'IsProcedure')=1)
BEGIN
  DROP PROCEDURE dbo.PRC_SUB_TEST;
END
GO
 
 
CREATE PROCEDURE dbo.PRC_SUB_TEST
AS
BEGIN
  
  SELECT * FROM #tmp_test;
 
  SELECT * FROM tempdb.dbo.sysobjects WHERE name LIKE '#tmp_test%'
  CREATE TABLE #tmp_test(name VARCHAR(128));
 
  INSERT INTO #tmp_test
  SELECT name FROM sys.objects
  SELECT * FROM tempdb.dbo.sysobjects WHERE name LIKE '#tmp_test%'
  SELECT * FROM #tmp_test;
END
GO

Then let’s take a look at the “scope” of the temporary table. Sorry, I use such a concept. There is no such concept in the official documents. This is only an aspect of our thinking, and there is no need to argue about the details. Let’s modify the stored procedure as shown below


IF EXISTS(SELECT 1 FROM sys.objects WHERE object_id= OBJECT_ID(N'dbo.PRC_SUB_TEST' ) AND OBJECTPROPERTY(object_id, 'IsProcedure')=1)
BEGIN
  DROP PROCEDURE dbo.PRC_SUB_TEST;
END
GO
CREATE PROCEDURE dbo.PRC_SUB_TEST
AS
BEGIN
  
  SELECT * FROM #tmp_test;
  CREATE TABLE #tmp_test(name VARCHAR(128));
 
  INSERT INTO #tmp_test
  SELECT name FROM sys.objects
 
  SELECT * FROM #tmp_test;
END
GO

Through experimental verification, we find that the temporary table of the outer stored procedure is effective in the inner stored procedure. Its “scope” is before the creation of the temporary table with the same name of the inner stored procedure, which is somewhat similar to the scope of global variables and local variables in high-level language.

Since two local temporary tables have been created, why are errors reported when modifying or parsing? A personal guess is that after the optimizer parses, during the execution process, when parsing or modifying, the database engine cannot judge or there is no such logic in the code to control which temporary table to retrieve. It may be a defect in the code or some logical reason. The above is only a personal guess and reasoning. Please correct any deficiencies or mistakes.

reference material:

https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2012/ms174979(v=sql.110)?redirectedfrom=MSDN

This is the end of this article about analyzing the temporary table monsters with the same name used in SQL Server nested stored procedures. For more information about SQL Server nested stored procedures, please search the previous articles of developeppaer or continue to browse the relevant articles below. I hope you will support developeppaer in the future!