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

Can't you just rewrite the statement?

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

Answer 2

drop table #temp1

select colx into #temp1 
from @tbl
where (ISNULL(@checkvar,'0')='0' or [colx] = @checkvar )

If @checkvar exists, it will use this where statement, else it will return all the data. You can change the '0' into anything u want, as long as it will never be the initial value of @checkvar.

Answer 3

If this is a stored procedure, SELECT .. INTO will cause a recompile of the procedure.

From what I understand, its better to create the table in the top of the procedure, and later do normal INSERTs.

So, Id suggest:

CREATE TABLE #temp1 (colx ...)

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  
BEGIN
  INSERT INTO #temp1(colx)
  SELECT colx 
  FROM @tbl
END
ELSE 
BEGIN
  INSERT INTO #temp1(colx)
  SELECT colx 
   FROM @tbl WHERE colx =@checkvar
END

Also, this way you get rid of the OR mentioned by other solutions (OR's are evil ;)

/B