跨會話同時執行的儲存過程中使用的全域臨時表

我在儲存過程中使用動態 SQL，並且必須使用全域臨時表，以便臨時表及其數據在sp_executesql發生時在實例外部可用。

我需要儲存過程能夠同時執行多次。我可以做這樣的事情嗎？

…

BEGIN TRY
WHILE OBJECT_ID('tempdb..##BOM', 'U') IS NOT NULL
BEGIN
WAITFOR DELAY '00:00:01'
END;

…這是儲存過程背後的一般思想。（這不是我使用的程式碼，只是我正在做的事情的要點）

CREATE TABLE ##BOM(...);
DECLARE @TBL AS TABLE(...);

INSERT INTO ##BOM
SELECT * FROM EXPLODE_BOM_F(@ORG, @JOB); --all data inserted into temp table for job

SET @SqlQuery = 'SELECT * FROM ##BOM LEFT JOIN STUFF WHERE 1=1 '; --build sql query
IF @Param IS NOT NULL 
  SET @SqlQuery = @SqlQuery + ' AND ##BOM.ParamField = @Param' --add to sql query if specific params are passed to SP

INSERT INTO @TBL (Fields, field...) --insert records into table
EXECUTE sp_Executesql @SqlQuery; --from final sql query

SELECT * FROM @TBL;

根據您在問題中的第二個程式碼片段，如果在您到達時表已經存在sp_executesql，那麼您不需要全域臨時表 ( ##table) 開始：本地臨時表 ( #table) 可以正常工作。本地臨時表可用於子流程，並且對它們（數據和/或模式）所做的任何更改都可用於所有級別，直到創建它的頂層。

如果該程式碼片段實際上不准確，我們會解釋以下內容：

我在儲存過程中使用動態 SQL，並且必須使用全域臨時表，以便在 sp_executesql 發生時臨時表及其數據在實例外部可用。

意味著臨時表是在某些時候在動態 SQL 中創建的，並且稍後需要對其他動態 SQL 可用，那麼您仍然不需要全域臨時表：只需在主程序範圍內創建本地臨時表，在任何動態 SQL 之前，只需使用動態 SQL 將其插入即可。

以下是問題中發布的程式碼，稍微修改為工作程式碼，並使用本地（非全域）臨時表：

IF (OBJECT_ID(N'tempdb..#BOM') IS NOT NULL)
BEGIN
 DROP TABLE #BOM;
END;

CREATE TABLE #BOM(ObjectID INT, ObjectName sysname, ParamField VARCHAR(3));
DECLARE @TBL AS TABLE (ObjectName sysname NOT NULL, ColumnName sysname NULL);
DECLARE @SqlQuery NVARCHAR(MAX),
       @Param NVARCHAR(100);

--SET @Param = 'v';

INSERT INTO #BOM (ObjectID, ObjectName, ParamField)
 SELECT [object_id], [name], [type]
 FROM   [msdb].[sys].[objects] so

SET @SqlQuery = 'SELECT tmp.ObjectName, sc.[name]
                FROM #BOM tmp
                LEFT JOIN msdb.sys.columns sc
                       ON sc.[object_id] = tmp.ObjectID
                WHERE 1=1 '; --build sql query

IF (@Param IS NOT NULL)
BEGIN
 SET @SqlQuery = @SqlQuery + ' AND tmp.ParamField = @InnerParam'; --add to sql query
END;

INSERT INTO @TBL (ObjectName, ColumnName) --insert records into table
 EXECUTE sp_Executesql @SqlQuery,
                       N'@InnerParam NVARCHAR(100)',
                       @InnerParam = @Param; --from final sql query

SELECT * FROM @TBL;

無論哪種情況，您都不需要創建一個通過@@SPIDGUID 或 GUID 強制執行程序分離的真實表。這只是需要清理的額外垃圾，而本地臨時表本質上是“執行緒安全的”並且會自動為您清理。

引用自：https://dba.stackexchange.com/questions/102157