Sql-server – if block fails to create temp table in a procedure

ddlsql servert-sqltempdbtemporary-tables

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

Best 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
Related Question