Using temp tables in IF .. ELSE statements

Question

Why does SQL Server insist that the temp table already exists! one or the other will happen!! , so it will never be the case.

declare @checkvar  varchar(10)
declare @tbl TABLE( colx varchar(10) )
set @checkvar ='a'

INSERT  INTO @tbl (colx) VALUES('a')
INSERT  INTO @tbl (colx) VALUES('b')
INSERT  INTO @tbl (colx) VALUES('c')
INSERT  INTO @tbl (colx) VALUES('d')

IF @checkvar  is null  select colx INTO #temp1 FROM @tbl
ELSE select colx INTO #temp1 FROM @tbl WHERE colx =@checkvar

error is :There is already an object named '#temp1' in the database.

Is there an elegant way around this? if @checkvar is null, i want the whole table otherwise, give me just the values where @checkvar = something

EDIT: the column is a varchar, not an int.

Answer 1

You can create an empty temp table with the desired structure by using WHERE 1=0. Then insert the desired records with your original code

SELECT colx INTO #temp1 
FROM   @tbl 
WHERE  1 = 0  // this is never true

IF @checkvar  IS NULL
BEGIN 
    INSERT INTO #temp1 (colName)   
    SELECT colx FROM @tbl 
END
ELSE 
BEGIN 
    INSERT INTO #temp1 (colName)   
    SELECT colx 
    FROM   @tbl 
    WHERE  colx = @checkvar 
END

Answer 2

Can't you just rewrite the statement?

SELECT colx INTO #temp1 FROM @tbl WHERE (@checkvar IS NULL) OR (colx = @checkVar)