CODEWITHSUNDEEP
sqlsql-servert-sql
JYousef
JYousef

Reputation: 181

T-SQL IF statement explanation

I'm really puzzled by this one!! I'm sure it's simple but really can't figure it out!!

DECLARE @jobid INT = 100
   IF (@JobID >= 0)
 BEGIN 
SELECT * into #tmp FROM Persons 
 end
    ELSE
 BEGIN
SELECT * into #tmp FROM Persons1 
 end

It gives an error that the #tmp table already exists! Why it would validate both statements !

Of course my original query is huge and doing more, but that's a sample to illustrate my error.

Can anybody explain it please?

The #tmp table is not there, even when you try to drop it or change the name, still the engine validates both statements!

I'm using 2008 R2.

Thanks Jason

Upvotes: 2

Views: 8373

Answers (2)

Oleg Dok
Oleg Dok

Reputation: 21756

You run it over Linked Server? Or you not deleted the one from previous run.

Try to create #tmp table prior to IF statement:

CREATE TABLE #tmp(fields...)

DECLARE @jobid INT = 100
IF (@JobID >= 0)
BEGIN 
  INSERT #tmp
  SELECT * FROM Persons 
end
ELSE
BEGIN
  INSERT #Tmp
  SELECT * FROM Persons1 
end

or delete provious one

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
  EXEC('DROP TABLE #tmp')
GO

DECLARE @jobid INT = 100
   IF (@JobID >= 0)
 BEGIN 
SELECT * into #tmp FROM Persons 
 end
    ELSE
 BEGIN
SELECT * into #tmp FROM Persons1 
 end

Upvotes: 2

sazh
sazh

Reputation: 1832

#tmp may already be created in tempDB from previous runs of your query. If you are not using #tmp anywhere in your query prior to that block, you could do something like the following prior to that block of code to ensure it's always ready to go:

IF OBJECT_ID('tempDB..#tmp') IS NOT NULL
    DROP TABLE #tmp

Upvotes: 2

Related Questions