在 IF .. ELSE 语句中使用临时表 [英] Using temp tables in IF .. ELSE statements

问题描述

为什么SQL Server 坚持临时表已经存在！一个或另一个会发生！，所以永远不会是这样.

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

错误是:数据库中已经有一个名为#temp1"的对象.

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

有没有优雅的方法来解决这个问题?如果@checkvar 为空，我想要整个表否则，只给我@checkvar = something

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

该列是一个 varchar，而不是一个 int.

the column is a varchar, not an int.

推荐答案

你就不能重写一下语句吗?

Can't you just rewrite the statement?

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

这篇关于在 IF .. ELSE 语句中使用临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！