Why does SQL raise an error if I have legal code that will never be reached?

Question

I have very strange behavior with the code below.

--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 am trying to achieve a more complex script, but I am having the problem of checking for the existence of a temporary table and creating it if necessary.

In some part of my code, I may or may not have already created a temporary table. So I check if it exists, and if it does not exist, I want to create it.

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

Why does uncommenting create table #temp (ID datetime) cause SQL to run the true part of the IF if it is always evaluated as false ?

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

Answer 1

Try as follows:

 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 during parsing, that is, before the request is actually executed. Replace this:

 create table #temp (ID datetime) 

with:

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

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