Global Temporary Table used in Stored Procedure executed concurrently across sessions

Question

I use dynamic SQL in a stored procedure and must use a global temp table so the temp table and its data is available outside the instance when sp_executesql happens.

I need the stored procedure to be able to be run multiple times simultaneously. Could I do something like this?

...

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

... Here is the general idea behind the stored procedure. (This is not the code I use just a gist of what I'm doing)

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;

Answer 1

Based on your 2nd code snippet in the question, if the table already exists by the time you get to the sp_executesql, then you didn't need a global temporary table (##table) to begin with: a local temporary table (#table) would work just fine. Local temporary tables are available to sub-processes, and any changes made to them (data and/or schema) are available to all levels, up to the top level where it was created.

If that code snippet is actually inaccurate, and we interpret the following:

I use dynamic SQL in a stored procedure and must use a global temp table so the temp table and its data is available outside the instance when sp_executesql happens.

to mean that the temporary table is created within Dynamic SQL at some point AND needs to be available to other Dynamic SQL later on, then you still don't need a global temporary table: just create the local temporary table in the main process scope, prior to any Dynamic SQL, and just use the Dynamic SQL to insert into it.

The following is the code posted in the question, modified slightly to be working code, AND working with a local (not global) temporary table:

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;

In either case, you don't need to create a real table that enforces process separation via @@SPID or a GUID. That is just extra junk that needs to be cleaned up whereas the local temporary table is inherently "thread safe" and is automatically cleaned up for you.

Answer 2

I used Mark Sinkinsons suggestion and used a real table with a GUID field for distinction between data. ** Using @srutzky answer worked much cleaner, while both answers worked @srutzky 's answer was more efficient.