浅析SQL Server的嵌套存储过程中使用同名的临时表怪像

SQL Server的嵌套存储过程，外层存储过程和内层存储过程（被嵌套调用的存储过程）中可以存在相同名称的本地临时表吗？如果可以的话，那么有没有什么问题或限制呢？在嵌套存储过程中，调用的是外层存储过程的临时表还是自己定义的临时表呢？是否类似高级语言的变量一样，本地临时表有没有“作用域“范围呢？

注意：也可以称呼为父存储过程和子存储过程，外层存储过程和内层存储过程。这些只是不同的称呼或叫法而已。我们这里统一使用外层存储过程和内层存储过程。后续文章部分不再述说。

我们先来看一个例子，如下所示，我们构造一个简单的例子。

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_TESTENDGOCREATE PROC dbo.PRC_TESTASBEGIN   CREATE TABLE #tmp_test(id INT);   INSERT INTO #tmp_test  SELECT 1;   SELECT * FROM #tmp_test;   EXEC PRC_SUB_TEST   SELECT * FROM #tmp_test   ENDGO 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;ENDGO CREATE PROCEDURE dbo.PRC_SUB_TESTASBEGIN    CREATE TABLE #tmp_test(name VARCHAR(128));   INSERT INTO #tmp_test  SELECT name FROM sys.objects   SELECT * FROM #tmp_test;ENDGO EXEC PRC_TEST;

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;ENDGO CREATE PROCEDURE dbo.PRC_SUB_TESTASBEGIN    CREATE TABLE #tmp_test(name VARCHAR(128));   INSERT INTO #tmp_test  SELECT name FROM sys.objects   SELECT name FROM #tmp_test;ENDGO

此时只要先我执行一次存储过程dbo.PRC_SUB_TEST，然后再去执行存储过程dbo.PRC_TEST就不会报错了。而且只要执行过一次这个存储过程，然后在当前会话或其它任何会话执行dbo.PRC_TEST都不会报错了。是否非常让人迷惑或不解。

EXEC dbo.PRC_SUB_TEST; EXEC PRC_TEST;

DBCC FREEPROCCACHE

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.

在存储过程或触发器中创建的本地临时表的名称可以与在调用存储过程或触发器之前创建的临时表名称相同。但是，如果查询引用临时表，而同时有两个同名的临时表，则不定义针对哪个表解析该查询。嵌套存储过程同样可以创建与调用它的存储过程所创建的临时表同名的临时表。但是，为了对其进行修改以解析为在嵌套过程中创建的表，此表必须与调用过程创建的表具有相同的结构和列名。下面的示例说明了这一点。

CREATE PROCEDURE dbo.Test2AS  CREATE TABLE #t(x INT PRIMARY KEY);  INSERT INTO #t VALUES (2);  SELECT Test2Col = x FROM #t;GO CREATE PROCEDURE dbo.Test1AS  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

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

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;ENDGO  CREATE PROCEDURE dbo.PRC_SUB_TESTASBEGIN    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;ENDGO

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;ENDGOCREATE PROCEDURE dbo.PRC_SUB_TESTASBEGIN    SELECT * FROM #tmp_test;  CREATE TABLE #tmp_test(name VARCHAR(128));   INSERT INTO #tmp_test  SELECT name FROM sys.objects   SELECT * FROM #tmp_test;ENDGO

既然创建了两个本地临时表，那么为什么修改或解析的时候就会报错呢？个人的一个猜测是，优化器解析过后，在执行过程中，解析或修改的时候，数据库引擎无法判断或者代码里面没有这种逻辑去控制检索哪一个临时表。有可能是代码里面的一个缺陷亦或是某种逻辑原因导致。上述仅仅是个人的一个猜测、推理。如有不足或不对的地方，敬请指正。

参考资料：

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

更多相关文章

随机推荐