if block fails to create temp table in a procedure

Posted on

Question :

I’m attempting to do this in a procedure:

DECLARE @a bit = 1;
BEGIN
    SELECT * INTO #aTemp FROM OPENROWSET( ... );

    IF @a = 0
    BEGIN
        SELECT ... INTO #bTemp FROM #aTemp;
    END
    ELSE
    BEGIN
        SELECT ... INTO #bTemp FROM #aTemp;
    END
END

I get the error:

Msg 2714, Level 16, State 1, Line 10
There is already an object named '#bTemp' in the database.

Why is this happening and is there a work around?

Update

I’ve attempted to add a DROP statement as suggested here, but it still does not work:

DECLARE @a bit = 1;
BEGIN
    SELECT * INTO #aTemp FROM OPENROWSET( ... );

    IF @a = 0
    BEGIN
        IF OBJECT_ID('[tempdb]..#bTemp') IS NOT NULL
        BEGIN
            DROP TABLE #bTemp;
        END

        SELECT ... INTO #bTemp FROM #aTemp;
    END
    ELSE
    BEGIN
        IF OBJECT_ID('[tempdb]..#bTemp') IS NOT NULL
        BEGIN
            DROP TABLE #bTemp;
        END

        SELECT ... INTO #bTemp FROM #aTemp;
    END
END

Answer :

Per the documentation:

If more than one temporary table is created inside a single stored procedure or batch, they must have different names.

I ended up creating the table before the IF block like so:

DECLARE @a bit = 1;
BEGIN
    IF OBJECT_ID('[tempdb]..#bTemp') IS NOT NULL
    BEGIN
        DROP TABLE #bTemp;
    END

    CREATE TABLE #bTemp (
        [c] int);

    IF @a = 0
    BEGIN
        INSERT INTO #bTemp
        SELECT 1 AS [c];
    END
    ELSE
    BEGIN
        INSERT INTO #bTemp
        SELECT 1 AS [c];
    END

    DROP TABLE #bTemp;
END

This is NOT an answer to the question, only a demonstration to @SebastienMeine how ##global temp tables can kill concurrency.

In one window, do this:

CREATE PROCEDURE dbo.floob1
  @p INT
AS
BEGIN
  SET NOCOUNT ON;

  SELECT a = @p INTO ##floob;
END
GO

CREATE PROCEDURE dbo.floob2
AS
BEGIN
  SET NOCOUNT ON;

  SELECT a FROM ##floob;
END
GO

EXEC dbo.floob1 @p = 1;
WAITFOR DELAY '00:01:00';
EXEC dbo.floob2;

Result:

a
----
1

Then open another window, and do this:

BEGIN TRY
  EXEC dbo.floob1 @p = 2;
END TRY
BEGIN CATCH
  PRINT 'Something bad happened.';
END CATCH
GO
EXEC dbo.floob1 @p = 2;
GO
EXEC dbo.floob2;

Results:

Something bad happened.

Msg 2714, Level 16, State 6, Procedure floob1

There is already an object named ‘##floob’ in the database.

a
----
1

So the ##table still exists even though the procedure it was created in has long since finished.

Then after the first window has finished, try running this portion in the first window again:

EXEC dbo.floob1 @p = 1;

Result:

Msg 2714, Level 16, State 6, Procedure floob1

There is already an object named ‘##floob’ in the database.

So the ##table still exists even though the same user is simply trying to create again.

This illustrates that:

  1. only one user can effectively call this procedure at a time
  2. the standard advice to not bother dropping #temp tables (which I am on the fence about) does not apply in the same way to ##temp tables.

Here is a solution which I use if temp table can’t be created upfront and don’t want to put core logic in dynamic SQL.

IF 1 = 1 -- Replace with actual condition
BEGIN
    SELECT * INTO #tmp1 FROM dbo.Table1
END
ELSE
BEGIN
    SELECT * INTO #tmp2 FROM dbo.Table2
END

-- Inserting data into global temp table so sql server can't complain on not recognizing in a context
DECLARE @Command VARCHAR(MAX)
IF OBJECT_ID('tempdb..#tmp1') IS NOT NULL
BEGIN
    SET @Command = 'SELECT * INTO ##tmp FROM #tmp1'
END
ELSE
BEGIN
    SET @Command = 'SELECT * INTO ##tmp FROM #tmp2'
END

EXECUTE(@Command)
SELECT * INTO #tmpFinal FROM ##tmp -- Again passing data back to local temp table from global temp table to avoid seeing red mark

IF OBJECT_ID('tempdb..##tmp') IS NOT NULL DROP TABLE ##tmp
IF OBJECT_ID('tempdb..#tmp1') IS NOT NULL DROP TABLE #tmp1
IF OBJECT_ID('tempdb..#tmp2') IS NOT NULL DROP TABLE #tmp2

SELECT * FROM #tmpFinal

IF OBJECT_ID('tempdb..#tmpFinal') IS NOT NULL DROP TABLE #tmpFinal

Leave a Reply

Your email address will not be published. Required fields are marked *