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)
DROP TABLE #BOM;
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),
--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)
SET @SqlQuery = @SqlQuery + ' AND tmp.ParamField = @InnerParam'; --add to sql query
INSERT INTO @TBL (ObjectName, ColumnName) --insert records into table
EXECUTE sp_Executesql @SqlQuery,
@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.