T-SQL error object exists when separated in if/else blocks

Question

I get the error: Msg 2714, Level 16, State 1, Line 16 There is already an object named '#mytemptable' in the database.

There are ways around it, but wonder why this happens. Seems like SQL Server is verifying both blocks of the if/else statement?

declare @choice int
select @choice = 1

if @choice = 1
    begin
        select 'MyValue = 1' AS Pick into #my_temp_table
    end
else
    begin
        select 'MyValue <> 1' AS Pick into #my_temp_table
    end

select * from #my_temp_table

drop table #my_temp_table

If the tables have different names, it works. Or if I create the temp table and use Insert Into... statements that works as well.

Answer 1

See here: What is deferred name resolution and why do you need to care?

basically you need to ceate the table first

So what is happening is that beginning with SQL server 7 deferred name resolution was enabled for real tables but not for temporary tables. If you change the code to use a real table instead of a temporary table you won’t have any problem

Here is another way

 declare @choice int
    select @choice = 1

declare @Value varchar(100)
 if @choice = 1
 select @Value = 'MyValue = 1'
 else
 select @Value = 'MyValue <> 1'

    select @Value AS Pick into #my_temp_table
    select * from #my_temp_table

    drop table #my_temp_table

Answer 2

Try this:

    declare @choice int
    select @choice = 1

CREATE TABLE     #my_temp_table(
Pick varchar(25)
)


    if @choice = 1
        begin
            INSERT INTO #my_temp_table 
              select 'MyValue = 1'
        end
    else
        begin
            INSERT INTO #my_temp_table 
                select 'MyValue <> 1'
        end

    select * from #temptable

    drop table #temptable

EDIT Sorry, I see that you tried this and the question was WHY does this happen. It is because SQL Server parses the stored procedure when it is created and checks for naming conflicts.