sql server - Drop temp table within IF ELSE statement

Question

I am facing a deadlock here, the issue is that I have to alter a procedure which makes use of 3 different temp tables. Lets for the sake of the conversation name them #temptable1, #temptable2, #temptable3. I cannot copy/paste the whole procedure here but the general idea is this, The original procedure (procedure1) creates the #temptable1 at the very beginning of the process

create table #temptable1

then creates the remaining two by using a select/into statement

select  T.Col
    ,   T.Col2
    ,   T.Col3
into   #temptable2
from   table1 T
where  T.BB>0

select  T.Col
    ,   T.Col2
    ,   T.Col3
into   #temptable3
from   table2 T
where  T.BB>0

drop table #temptable1
drop table #temptable2
drop table #temptable3

Until this point it works fine, but what I want to do is to alter the procedure by adding an if/else statement. Thus to look like that,

declare @BBB nvarchar(32)

create table #temptable1


if @BBB='dd'

begin 

select  T.Col
        ,   T.Col2
        ,   T.Col3
    into   #temptable2
    from   table1 T
    where  T.BB>0 and T.G='FDL'

    select  T.Col
        ,   T.Col2
        ,   T.Col3
    into   #temptable3
    from   table2 T
    where  T.BB>0 and T.G='FDL'

    drop table #temptable1
    drop table #temptable2
    drop table #temptable3
end 

if @BBB='kk'

begin 

select  T.Col
        ,   T.Col2
        ,   T.Col3
    into   #temptable2
    from   table1 T
    where  T.BB>0 and T.G='FD'

    select  T.Col
        ,   T.Col2
        ,   T.Col3
    into   #temptable3
    from   table2 T
    where  T.BB>0 and T.G='FD'

    drop table #temptable1
    drop table #temptable2
    drop table #temptable3
end

else 

begin 

select  T.Col
        ,   T.Col2
        ,   T.Col3
    into   #temptable2
    from   table1 T
    where  T.BB>0

    select  T.Col
        ,   T.Col2
        ,   T.Col3
    into   #temptable3
    from   table2 T
    where  T.BB>0

    drop table #temptable1
    drop table #temptable2
    drop table #temptable3
end

When I try to create the new procedure I get this message,

Msg 2714, Level 16, State 1, Procedure pPortfoliostest3, Line 412
There is already an object named '#temptable1' in the database.
Msg 2714, Level 16, State 1, Procedure pPortfoliostest3, Line 550
There is already an object named '#temptable2' in the database.
Msg 2714, Level 16, State 1, Procedure pPortfoliostest3, Line 711
There is already an object named '#temptable3' in the database.

The lines are indicating where the temp tables are within the second if statement (if @BBB='kk'). I have tried different combinations but to no avail. Any tips? Thank you for your time.

Answer 1

The T-SQL parser is remarkably primitive. In particular, control flow doesn't affect when object names come into scope and remain in scope.

So the names you're using in your if branch are still in scope and cause a conflict when the else branch is parsed.

If possible, move the table definitions up to the top of the stored procedure, before any control flow, and switch to INSERT ... SELECT ... rather than SELECT ... INTO ...; or if the table definitions don't match between the if and else branches, you'll need to use different names for the temp tables.

---

As another example of how primitive the parser is, consider the following:

if 1=0
begin
    declare @a int
end
select @a

This produces a result set containing null rather than (as you might have expected) an error saying that @a isn't declared.