SQL Server WHERE中的情况 [英] CASE in WHERE, SQL Server
问题描述
我正在搜索,用户在下拉菜单中选择了一些子句.当他们将某些框留空时,我希望查询忽略子句.我知道CASE,并且最好想到的是,如果我在存储过程中将0传递给参数,它将忽略该参数,就像这样.
I'm doing some search, where users are choosing in dropdown some clauses. When they leave some box empty, I want query to ignore clause. I know CASE, and best I thought of is that if I pass 0 to parameter in stored procedure, it ignores that parameter, like this.
WHERE a.Country = (CASE WHEN @Country > 0 THEN @Country ELSE (something else) END)
所以,(其他)应该没有条件,它可以是'> 0',因为国家/地区ID来自所有> 1,但是我不知道如何在同一情况下使用>和=.
so, (something else) should be like no condition, it can be '>0' as country ids are from all >1, but I don't know how to use > and = in same CASE.
有什么建议吗?
推荐答案
几种方法:
-- Do the comparison, OR'd with a check on the @Country=0 case
WHERE (a.Country = @Country OR @Country = 0)
-- compare the Country field to itself
WHERE a.Country = CASE WHEN @Country > 0 THEN @Country ELSE a.Country END
或者,使用动态生成的语句,并仅在适当的情况下添加国家/地区"条件.从仅在实际需要应用的条件下执行查询的意义上来说,这应该是最有效的,并且如果存在支持索引,则可以导致更好的执行计划.您将需要使用参数化的SQL来防止SQL注入.
Or, use a dynamically generated statement and only add in the Country condition if appropriate. This should be most efficient in the sense that you only execute a query with the conditions that actually need to apply and can result in a better execution plan if supporting indices are in place. You would need to use parameterised SQL to prevent against SQL injection.
这篇关于SQL Server WHERE中的情况的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!