访问在过程外部但在同一事务内的存储过程中创建的临时表

首先,您无法访问存储过程外部SP中定义的本地临时表。它总是超出范围。

其次,你可能甚至不需要临时表格。在您的示例中:

SET @test = (SELECT ChangeID FROM #CurrentChangeID)

看起来您只需要一个值。

我建议使用输出参数。

CREATE PROCEDURE [dbo].[GetChangeID](
      @test UNIQUEIDENTIFIER OUTPUT
)
AS
BEGIN
     -- ...

    SET @test =  '00000000-0000-0000-0000-000000000000';

END;

并拨打:

DECLARE @changeID uniqueidentifier
EXEC dbo.GetChangeID @chaneId OUTPUT;

SELECT @changeId;

修改的存储过程:

CREATE PROCEDURE [dbo].[GetChangeID]()
AS
BEGIN
    DECLARE @ChangeID uniqueidentifier
    ...
    Code that generates the uniqueidentifier, @ChangeID.
    ...

    --This can be seen within the context of this batch.
    SET CONTEXT_INFO @ChangeID
END
GO

SELECT CONTEXT_INFO as changeID
FROM sys.dm_exec_requests
WHERE session_id = @@SPID AND request_id = CURRENT_REQUEST_ID()