今天发现临时表可以被覆盖掉

Question

临时表为存储过程单独存在，过程完，会自动清除。今天遇到个问题，临时表被覆盖。

过程1

begin

exec 过程2

end

过程2内嵌在过程1中，两个过程中都有临时表，名称也相同：#tmp，在查过程2时，有时会报错找不到列，有时候不会，好像很不稳定。既然临时是为过程独立存在，那么这两个即使名字相同也不应该有影响啊，搞不明白。

Answer 1

這是引擎引起的，解決方法如下

如果 存儲過程2是into #臨時表 沒問題

這樣會出錯,當臨時表的結構相同時不會出錯

USE Test
GO
IF object_id('P2','P') IS NOT NULL
DROP PROC p2
go
create PROC p2
AS
CREATE TABLE #(Col INT)
INSERT # SELECT 1 AS Col
SELECT * FROM #
--SELECT 1 AS col INTO #
SELECT * FROM #
DROP TABLE #
GO
IF object_id('P1','P') IS NOT NULL
DROP PROC p1
go
create PROC P1
AS
CREATE TABLE #(Col INT,Col2 int)
INSERT # SELECT 1 AS COl,2 AS Col2
EXEC P2
SELECT * FROM #
DROP TABLE #
GO

exec p1

這樣不出錯

USE Test
GO
IF object_id('P2','P') IS NOT NULL
DROP PROC p2
go
create PROC p2
AS
SELECT 1 AS col INTO #
SELECT * FROM #
DROP TABLE #
GO
IF object_id('P1','P') IS NOT NULL
DROP PROC p1
go
create PROC P1
AS
CREATE TABLE #(Col INT,Col2 int)
INSERT # SELECT 1 AS COl,2 AS Col2
EXEC P2
SELECT * FROM #
DROP TABLE #
GO

exec p1

---

ROY WU(吳熹)

Answer 2

这是由于临时表创建级别不一样，出现临时表重名时候发生的错误。

这是技术内幕上的一个例子

SET NOCOUNT ON;
 USE tempdb;
 GO
 IF OBJECT_ID('dbo.proc1') IS NOT NULL
  DROP PROC dbo.proc1;
 GO
 IF OBJECT_ID('dbo.proc2') IS NOT NULL
  DROP PROC dbo.proc2;
 GO
 CREATE PROC dbo.proc1
 AS
 CREATE TABLE #T1(col1 INT NOT NULL);
 INSERT INTO #T1 VALUES(1);
 SELECT * FROM #T1;
 
 EXEC dbo.proc2;--注意这里我调用了RPOC2
 GO
 
 CREATE PROC dbo.proc2
 AS
 CREATE TABLE #T1(col1 INT NULL);--这里再次创建了#T1
 INSERT INTO #T1 VALUES(2);
 SELECT * FROM #T1;
 GO
 
 -- 调用存储过程1
 EXEC dbo.proc1;
 GO
 /*
 col1
 -----------
 1
 col1
 -----------
 2
 */
---这里似乎没出问题.这里他们的表结构式一样的,注意.
 --现在情况变了,我去修改下第二个存储过程中#T1的表结构,看看情况还是否一样.
ALTER PROC dbo.proc2
 AS
 
 CREATE TABLE #T1(col1 INT NULL, col2 INT NOT NULL);
 INSERT INTO #T1 VALUES(2, 2);
 SELECT * FROM #T1;
 GO
 
 -- 运行,出现错误.
 EXEC dbo.proc1;
 GO
 /*
 col1
 -----------
 1
 
 消息 213，级别 16，状态 1，过程 proc2，第 5 行
 列名或所提供值的数目与表定义不匹配。
 */

这里的解释是:当你的批处理被解析时候,你创建的临时表是不可以用的,它要等到运行时候才被解析.如果你引用的临时表已经存在了,它
 不会智能地去帮你判断下你本身是否有这么个临时表,而是直接去解析那个已经存在的临时表.然后就悲剧般的使用已经存在的表.
 我们回到我们这个题目,当PROC2被PROC1调用的时候,因为#t1表已经在PROC1中存在了,那么它在解析你的PROC2中的INSERT INTO #T1 VALUES(2, 2)
 的时候去插入的是PROC1中的#T1。这个时候你就报错了.

解决方法就是：先去运行下PROC2 然后在执行  EXEC dbo.proc1; 就是你要的了.这是因为先执行PROC2，他的执行计划就存在缓存里了.

所以 不要再在不同级别里取相同名字的临时表

Answer 3

过程1

begin

exec 过程2

end

过程2可以引用过程1中的临时表的, 不稳定应该看你的脚本到底是怎么写的

另外, 如果过程2中创建与过程1同名的临时表, 则过程2中使用的时候, 是使用过程2的临时表

一般不要在嵌套的过程中使用相同名称的临时表.