SQL SERVER：检查变量是否为空，然后为 Where 子句分配语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16086322/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL SERVER: Check if variable is null and then assign statement for Where Clause
提问by Krishh
I am trying to achieve something like the below in WHERE clause in sql.
我正在尝试在 sql 的 WHERE 子句中实现类似下面的内容。
if (@zipCode ==null)
begin
([Portal].[dbo].[Address].Position.Filter(@radiusBuff) = 1)
end
else if(@zipCode !=null)
begin
([Portal].[dbo].[Address].PostalCode=@zipCode )
end
I tried the following:
我尝试了以下方法：
WHERE ((@zipCode IS NOT NULL AND ([Portal].[dbo].[Address].PostalCode=@zipCode)) OR (@zipCode IS NULL AND ([Portal].[dbo].[Address].Position.Filter(@radiusBuff) = 1)))
which is wrong. Can anyone help in framing the exact statement. Thanks!
这是错误的。任何人都可以帮助制定确切的声明。谢谢！
采纳答案by djangojazz
Isnull() syntax is built in for this kind of thing.
Isnull() 语法是为这种事情内置的。
declare @Int int = null;
declare @Values table ( id int, def varchar(8) )
insert into @Values values (8, 'I am 8');
-- fails
select *
from @Values
where id = @Int
-- works fine
select *
from @Values
where id = isnull(@Int, 8);
For your example keep in mind you can change scope to be yet another where predicate off of a different variable for complex boolean logic. Only caveat is you need to cast it differently if you need to examine for a different data type. So if I add another row but wish to specify int of 8 AND also the reference of text similar to 'repeat' I can do that with a reference again back to the 'isnull' of the first variable yet return an entirely different result data type for a different reference to a different field.
对于您的示例，请记住，您可以将范围更改为另一个 where 谓词关闭复杂布尔逻辑的不同变量。唯一需要注意的是，如果您需要检查不同的数据类型，则需要进行不同的转换。因此，如果我添加另一行但希望指定 int of 8 以及类似于“repeat”的文本引用，我可以通过再次引用回到第一个变量的“isnull”来做到这一点，但返回完全不同的结果数据类型对不同领域的不同引用。
declare @Int int = null;
declare @Values table ( id int, def varchar(16) )
insert into @Values values (8, 'I am 8'), (8, 'I am 8 repeat');
select *
from @Values
where id = isnull(@Int, 8)
and def like isnull(cast(@Int as varchar), '%repeat%')
回答by marceljg
is null is the syntax I use for such things, when COALESCE is of no help.
is null 是我用于此类事情的语法，当 COALESCE 没有帮助时。
Try:
尝试：
if (@zipCode is null)
begin
([Portal].[dbo].[Address].Position.Filter(@radiusBuff) = 1)
end
else
begin
([Portal].[dbo].[Address].PostalCode=@zipCode )
end
回答by Travis
Try a case statement
尝试使用 case 语句
WHERE
CASE WHEN @zipCode IS NULL THEN 1
ELSE @zipCode
END
回答by Raj Kumar
is nullcan be used to check whether nulldata is coming from a query as in following example:
is null可用于检查null数据是否来自查询，如下例所示：
declare @Mem varchar(20),@flag int
select @mem=MemberClub from [dbo].[UserMaster] where UserID=@uid
if(@Mem is null)
begin
set @flag= 0;
end
else
begin
set @flag=1;
end
return @flag;
回答by Raj Kumar
Try the following:
请尝试以下操作：
if ((select VisitCount from PageImage where PID=@pid and PageNumber=5) is NULL)
begin
update PageImage
set VisitCount=1
where PID=@pid and PageNumber=@pageno
end
else
begin
update PageImage
set VisitCount=VisitCount+1
where PID=@pid and PageNumber=@pageno
end
