1

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;
  • Can't you just use a real table with a Session ID field? – Mark Sinkinson May 21 '15 at 14:31
  • You know sometimes I think I over think things... Thanks! – Travis Peterson May 26 '15 at 17:00
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.

  • The table exists inside that same stored procedure. I'll add more to my 2nd snippet for clarification. I was receiving the error that the #BOM table did not exist when I tried it with a regular temp table. – Travis Peterson Jun 17 '15 at 16:46
  • @TravisPeterson Either you are not giving the full context of what you are trying to accomplish, or you had some syntax error. I have updated the code in my answer to match your updated code from the question. As you can see, a local temp table works just fine :-). – Solomon Rutzky Jun 18 '15 at 5:04
  • 1
    Wow, you're absolutely right it does work. I don't know what I was doing wrong before but using a #tmptbl is working now. Previously I was getting a message stating #tmptbl didn't exist.. – Travis Peterson Jun 19 '15 at 13:54
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.