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

Time:2021-2-22

 

   Can there be local temporary tables with the same name in nested stored procedures, outer stored procedures and inner stored procedures of SQL Server? If so, are there any problems or restrictions? In a nested stored procedure, do you call the outer stored procedure’s temporary table or the self-defined temporary table? Is the local temporary table similar to the high-level language variableScopeWhat about the 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. We use outer stored procedure and inner stored procedure uniformly here. The following part of the article will not be described.

 

Let’s start with an example. As shown below, we construct aA 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;

 

clip_image001

 

The simple test seems to be normal and nothing wrong. If you come to a conclusion at this time, it’s too early! For example, if you see a swan is white, if you come to a conclusion:All swans are whiteIn fact, there are black swans in the world, but you haven’t seen them! Let’s modify the stored procedure as shown belowdbo.PRC_SUB_TEST, using the field namenamereplace*, 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 proceduredbo.PRC_TESTIf so, an error will be reported:Invalid column name ‘name’.

 

clip_image002

 

At this point, I only need to execute the stored procedure oncedbo.PRC_SUB_TESTAnd then execute the stored proceduredbo.PRC_TESTYou won’t make a mistake. And the stored procedure is executed only once, and then in the current session or any other sessiondbo.PRC_TESTNo mistake. 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 do it through the following stepsSQLOr delete/This phenomenon can only be reproduced by reconstructing the stored procedure. It’s kind of ghostly.

 

DBCC FREEPROCCACHE

 

 

  This phenomenon is described in the official document (see the link to 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.

 

A local temporary table created in a stored procedure or trigger can have the same name as a temporary table created before the stored procedure or trigger was called. However, if the query references a temporary tableIf there are two temporary tables with the same name at the same time, it does not define which table to resolve the query. A nested stored procedure can also create a temporary table with the same name as the temporary table created by the calling stored procedure. However, in order to modify it to resolve to a table created during nesting,This table must have the same structure and column name 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 official documentsIf there are two temporary tables with the same name at the same time, it does not define which table to resolve the queryThis kind of explanation is still a bit 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 you want to modify or parse them in the memory process (modification is easy to understand, such as adding index, adding field and other DDL operations; about parsing, querying temporary tables, specifying field names in SQL, you need to To analyzeresolve)In this case, the temporary table must have the same table structure,Otherwise, it will report an error. The official document tells you that you can’t, but it doesn’t tell you why. So we might as well make some speculation about whether two local temporary tables are created in the nested calls of stored procedures? Is it possible to actually create only one local temporary table? What about local temporary table reuse? Let’s verify it briefly. As shown below, we can judge 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%';

 

clip_image003

 

 

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

 

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 temporary tableScopeI’m sorry to use such a concept. The official documents don’t have such a concept. It’s just one aspect of our thinking. There’s no need to argue about 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 procedureScopeBefore the creation of a temporary table with the same name of an inner stored procedure, this is similar to the scope of global variables and local variables in high-level languages.

 

clip_image004

 

Since two local temporary tables have been created, why do you report errors when modifying or parsing them? One of my personal guesses is that after the optimizer parses, in the process of execution, 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 just a personal guess and reasoning. If there is any deficiency or mistake, please correct.

 

 

reference material:

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