logo标签列表

在嵌套存储过程中创建重复的临时表

sql-serverstored-procedures
7
这里是情况:
Procedure 1创建了一个临时表(#MYTABLE)，并调用了Procedure 2。Procedure 2也试图创建#MYTABLE，但是列不同。当Procedure 2尝试向#MYTABLE中插入数据时，会出现报错，报错信息是“无效的列名”。我有两个问题：
1) #MYTABLE在Procedure 2内部创建时，系统应该不是也应该报错吗？我知道编译无法检测到这个问题，但是运行时我希望看到一个错误。
2) 给定它对创建没有任何抱怨，并且实际上，当你在Procedure 2内选择#MYTABLE时，你会看到新的列，那么为什么它会对插入操作报错？
以下是代码。取消注释任何一个插入语句都会出现错误。
（我知道很多方法可以解决这种情况，所以我不需要关于此的回复。我只想了解正在发生什么。）
IF OBJECT_ID(N'dbo.MYPROC1', N'P') IS NOT NULL
    DROP PROCEDURE dbo.MYPROC1;
GO

CREATE PROCEDURE dbo.MYPROC1
AS
    CREATE TABLE dbo.#MYTABLE ( Name VARCHAR(256) );

    SELECT
        'DO NOTHING 1' AS TABLENAME;

    EXEC dbo.MYPROC2;

GO

IF OBJECT_ID(N'dbo.MYPROC2', N'P') IS NOT NULL
    DROP PROCEDURE dbo.MYPROC2;
GO

CREATE PROCEDURE dbo.MYPROC2
AS
    SELECT
        'INSIDE PROC 2 BEFOREHAND' AS TABLENAME
       ,*
    FROM
        dbo.#MYTABLE;

    CREATE TABLE dbo.#MYTABLE
        (
         Name VARCHAR(256)
        ,LastName VARCHAR(256)
        );

    --INSERT  INTO dbo.#MYTABLE
    --        ( Name, LastName )
    --        SELECT
    --            'BARACK'
    --           ,'OBAMA';

    SELECT
        'INSIDE PROC 2 AFTERWARDS' AS TABLENAME
       ,*
    FROM
        dbo.#MYTABLE;

    --INSERT  INTO dbo.#MYTABLE
    --        ( Name, LastName )
    --        SELECT
    --            'BARACK'
    --           ,'OBAMA';

    SELECT
        'DO NOTHING 2' AS TABLENAME;

GO

EXEC MYPROC1;
4个回答
6
从创建表文档中得知：
在存储过程或触发器内创建的本地临时表可以与在调用存储过程或触发器之前创建的临时表同名。但是，如果查询引用了一个临时表，并且此时存在两个同名的临时表，则无法确定查询将解析为哪个表。嵌套存储过程也可以创建与调用它的存储过程创建的临时表同名的临时表。但是，要使修改解析为在嵌套过程中创建的表，该表必须具有与调用过程中创建的表相同的结构和列名。
2

1）在Procedure 2内创建#MYTABLE时，系统不应该出现错误提示吗？我理解为什么它无法在编译时进行检查，但在运行时我希望会出现错误。

不需要。您将获得2个本地临时表，可以查看它们的名称：

CREATE PROCEDURE dbo.MYPROC1
AS
    CREATE TABLE dbo.#MYTABLE ( Name VARCHAR(256) );
    EXEC dbo.MYPROC2;
GO

CREATE PROCEDURE dbo.MYPROC2
AS
    CREATE TABLE dbo.#MYTABLE(
         Name VARCHAR(256)
        ,LastName VARCHAR(256));

    SELECT *
    FROM tempdb.INFORMATION_SCHEMA.TABLES
    WHERE [Table_name] LIKE '%MYTABLE%' 
GO

SqlFiddle演示

输出：

╔════════════════╦═══════════════╦═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╦════════════╗
║ TABLE_CATALOG  ║ TABLE_SCHEMA  ║                                                            TABLE_NAME                                                             ║ TABLE_TYPE ║
╠════════════════╬═══════════════╬═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╬════════════╣
║ tempdb         ║ dbo           ║ #MYTABLE____________________________________________________________________________________________________________000000000117  ║ BASE TABLE ║
║ tempdb         ║ dbo           ║ #MYTABLE____________________________________________________________________________________________________________000000000118  ║ BASE TABLE ║
╚════════════════╩═══════════════╩═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╩════════════╝

2）考虑到它没有抱怨创建，事实上，当你在第二个存储过程中从#MYTABLE中进行SELECT时，你可以看到新的列，那么为什么它会抱怨INSERT呢？

因为SQL Server从外部存储过程获取第一个表定义。由于具有不同的列，所以在INSERT期间会出现错误。

2

1) #MYTABLE在Procedure 2中创建，系统不应该报错吗？我知道为什么它无法在编译时检测到问题，但是在运行时我期望会出现错误。

实际上，系统在编译时就已经报错了。当编译dbo.MYPROC2时，系统发现该表在父级作用域中已经存在，并且与你使用的列列表不兼容。如果没有可见的同名父对象，则该语句的编译将被推迟，直到执行CREATE TABLE之后。

如果你从dbo.MYPROC2中删除初始的SELECT语句，然后先执行dbo.MYPROC2再执行dbo.MYPROC1，则可能会成功——因为它已经有了dbo.MYPROC2的缓存计划，无需重新编译。

然而，我不建议这样做，除非你喜欢在缓存计划被清除并且过程以错误的顺序执行时出现随机错误。最好使用唯一的名称。

哎呀，谢谢你指出我在“编译时间”这个问题上的措辞不准确。我应该说得更明确一些，比如“当发生CREATE PROCEDURE时”。 - Daphne B
-1

好的，一开始看起来你的假设是正确的，但仅限于第一次。

当你创建名为MyTable的临时表时，SQL Server会在TEMPDB中创建一个实际的表，它的名称类似于'MyTable_____________...._____01D'，因此当任何其他代码使用相同的名称但在不同范围内创建表时，服务器可以区分它们。

在你的情况下，你在两个不同的范围内创建了本地临时表-两个不同的过程，无论其中一个调用另一个，你都无法从第一个过程访问在第二个过程中创建的表。

我建议你从sys.objects中选择数据，这样你可以看到有两个实际且不同的表被创建 - select name from tempdb..sysobjects where name like 'MYTABLE%'

最后 - 你使用相同的名称并期望访问“最小”作用域的表，但实际上服务器使用的是先创建的表。假设SQL服务器只从sys.objects中选择与当前匹配的作用域和名称匹配的前1个。

这个答案很好地解释了问题＃1，谢谢。但我仍然不明白为什么INSERT和SELECT的行为不同。为什么SELECT“看到”更新的表而INSERT“看到”旧表？ - Daphne B
嗯，那是个好问题，我无法回答它（抱歉，在回复您的帖子时我没有完全阅读它）。这可能应该由更有能力的团队来处理，他们了解 SQL Server 内部运行的情况。我将尝试在不同版本的 SQL Server 上运行相同的示例，并查看其表现如何。您使用哪个版本？ - GSazheniuk
“你无法从第一个过程中访问第二个过程创建的表。”这是不正确的。临时表具有动态范围，它们的作用域不像变量那样。一旦创建了表，其他过程就可以读取和写入它。尽管读取似乎被遮蔽了。 - Shannon Severance
@ShannonSeverance，如果您直接通过tampdb中的表名访问表，则可以。否则，您无法从父范围中选择在子范围（在这种情况下从另一个存储过程调用的存储过程）中创建的本地临时表。 - GSazheniuk
父进程无法访问子进程的本地临时表，因为该表在子过程退出时被销毁。但是，这是子进程访问父进程本地临时表的情况，它可以这样做而不必使用完整的tempdb名称。只要子进程不创建自己的冲突临时表即可。 - Shannon Severance
显示剩余2条评论
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接