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屋！