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

Question

情况如下:

过程 1 创建临时表 (#MYTABLE) 并调用过程 2。过程 2 还尝试创建具有不同列的 #MYTABLE。当过程 2 尝试将数据插入 #MYTABLE 时，会发生错误，提示“列名无效”。我对此有两个问题:

1) 当在过程 2 中创建 #MYTABLE 时，系统不应该提示吗？我理解为什么它不能在编译时反对，但在运行时我预计会出现错误。

2) 鉴于它不会提示创建，事实上，当您在过程 2 中从 #MYTABLE 进行 SELECT 时，您会看到新列，为什么它会提示 INSERT？

下面是代码。取消注释任一 INSERT 语句都会出现错误。

(我知道很多方法可以解决这种情况，所以我不需要对此的回应。我只是想了解发生了什么。)

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;

Answer 1

来自Create Table文档:

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.