浅析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_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;

简单测试似乎正常，并没有发现什么问题。如果此时你就下一个结论的话，那么就为时过早了！打个比方，你看见一只天鹅是白色的，如果你下了一个定论：“所有天鹅都是白色的”，其实这个世界真的有黑天鹅，只是你没有见过而已！如下所示，我们修改一下存储过程dbo.PRC_SUB_TEST，使用字段名name替换*，如下所示：

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

然后重复上面测试，如下所示，此时执行存储过程dbo.PRC_TEST的话，就会报错：“Invalid column name 'name'.”

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

EXEC dbo.PRC_SUB_TEST;
 
EXEC PRC_TEST;

如果你要再次重现这个现象的话，只能通过下面SQL或者删除/重建存储过程的方式，才能重现这个现象。似乎有点幽灵现象的感觉。

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.

阅读剩余部分