Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
Depending on what you're trying to do the workaround might be to use Table Variables. –  dcaswell Sep 11 '13 at 0:10
    
Can you describe what is different when @a is 0 and when it isn't 0? –  Aaron Bertrand Sep 11 '13 at 13:29

2 Answers 2

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.
share|improve this answer
    
If I read this correctly, I should continue to check and drop any ## tables used in my procedure? –  Kermit Sep 11 '13 at 16:05
    
@FreshPrinceOfSO yes, unless you can guarantee that no two users will ever call this stored procedure at the same time, or that the same user might not retry on the same connection (or while their original connection is still active), you should treat ##global tables like permanent tables - since, while they exist, that's essentially what they are. Or - better yet - simply don't use ##global temp tables. –  Aaron Bertrand Sep 11 '13 at 16:12
2  
Thanks, @AaronBertrand. When I think about concurrency I think about locking and blocking so this did not come to mind, but you have a very valid point. –  Sebastian Meine Sep 11 '13 at 17:22
up vote 2 down vote accepted

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
share|improve this answer
    
Inside a procedure you don't need to drop the temp table. In fact, you should not, as there can be a significant performance penalty for it. Just create the temp table at the beginning of the procedure and use it throughout. It will automatically go out of scope at the end of the procedure. You also don't need to do the IF EXISTS...DROP at the beginning as any existing temp table with the same name that was created outside of the procedure will be eclipsed inside. (See sqlity.net/en/1109/temp-tables-scoping-eclipsing for details.) –  Sebastian Meine Sep 11 '13 at 0:22
    
@SebastianMeine Did not know. Very useful information. Will definitely practice this going forward. Does going out of scope also apply for ## tables? –  Kermit Sep 11 '13 at 1:38
    
Don't use ## tables. The scoping is a bit different but, more importantly, they effectively change concurrency to exactly one user. –  Aaron Bertrand Sep 11 '13 at 3:25
    
@SebastianMeine - Can you elaborate on the "significant performance penalty"? AFAIK if the #temp table is able to be cached it will still be cached anyway even if a DROP statement is encountered. –  Martin Smith Sep 11 '13 at 13:59
    
@MartinSmith, the exact rules around this are a little wage and I have not done extensive testing on it. You are certainly running the lowest risk by creating the table once and not in any way altering or dropping it afterwards. That also gives you the added benefit of less clutter in your code. –  Sebastian Meine Sep 11 '13 at 15:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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