Why SQL throws an error if I have a legal code the never will be reached?

Question

I've got a very strange behavior with the below code.

--IMPORTANT: Do not use 'GO' since it will terminate
--the batch and it is only usable in Microsoft tools
--and not in the code itself.
--I don't really need a workaround I want to know why
--this behavior happens.

--Create the first time if doesn't exists

IF OBJECT_ID('tempdb.dbo.#temp') IS NULL
    begin
        create table #temp (ID datetime)        
    end


--I've just created so it should evaluates as False
IF OBJECT_ID('tempdb.dbo.#temp') IS NULL
    begin
        print 'does not exists'
        --uncomment the below line and you will see
        --an error saying table already exists
        --even though the IF was evaluate as TRUE

        --create table #temp (ID datetime)      
    end
else
    begin
        print 'exists'
    end

I'm trying to achieve a more complex script but I end up with a problem to verify if a temporary table exists and create it if necessary.

In some part of my code I can have or no a temporary table already created. So I check if it exists and if doesn't exists I want to create it.

The problem is that if I only print the message it evaluates as exists but if I uncomment the part where it does not exists and create a new one it is avoided to run because it say it already exists.

Why uncommenting create table #temp (ID datetime) make SQL run the true part of the IF statement if it always evaluates as false?

I'm running SQL Server 2008 (10.50.2500) in SQL Management Studio 11.0.2100.60

Answer 1

Try this way:

IF OBJECT_ID('#temp') IS NOT NULL
    begin
        exec('drop table #temp ')        
    end
go
create table tempdb..#temp (ID datetime)

IF OBJECT_ID('#temp') IS NULL
    begin
        select 'does not exists'

    end
else
    begin
        select 'exists'
    end

or

IF OBJECT_ID('tempdb.dbo.#temp') IS NULL
    begin
        exec('create table #temp (ID datetime)')        
    end




--I've just created so it should evaluates as False
IF OBJECT_ID('tempdb.dbo.#temp') IS NULL
    begin
        print 'does not exists'
        --uncomment the below line and you will see
        --an error saying table already exists
        --even though the IF was evaluate as TRUE

        --create table #temp (ID datetime)      
    end
else
    begin
        print 'exists'
    end

Answer 2

Your error occurs at parse time, that is, before the query is actually executed. Replace this:

create table #temp (ID datetime) 

with:

exec('create table #temp (ID datetime)')

Since exec creates a new scope, the create table is only parsed when the temporary table does not exist.