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

  • 常规讨论

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

    过程1

    begin

    exec 过程2

    end

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

    2010年4月29日 9:49

全部回复

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

    如果 存儲過程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(吳熹)
    2010年4月29日 10:05
    版主
  • 这是由于临时表创建级别不一样,出现临时表重名时候发生的错误。

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

    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,他的执行计划就存在缓存里了.

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

    2010年4月29日 11:55
  • 过程1

    begin

    exec 过程2

    end

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

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

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

    2010年4月30日 4:11