SQL查询WHERE子句如何用CASE处理@var1、@var2可空的多条件匹配问题

错误原因说明 #

你写的CASE语法不符合SQL规范：SQL中的CASE是返回具体值的表达式，不能直接返回AND 条件这类SQL语法片段；同时你写的CASE逻辑是互斥的，就算语法合法，当@var1和@var2同时非空时，第二个参数的过滤条件也不会生效。

正确实现方案 #

最通用的写法是通过OR短路逻辑实现「参数为空则跳过该过滤规则」，无需使用CASE：

SELECT 
 * 
FROM 
 Table Tb 
WHERE
  Tb.LastModificationDate BETWEEN @StartDate And @EndDate 
  AND (@var1 IS NULL OR Tb.var1 = @var1)
  AND (@var2 IS NULL OR Tb.var2 = @var2)

逻辑说明 #

• 每个参数对应独立的判断单元，当参数为NULL时，@参数 IS NULL判断为真，OR连接下整个括号内的条件整体为真，相当于不添加该字段的过滤规则
• 当参数非NULL时，会执行表字段 = @参数的匹配判断，只有符合条件的行才会被返回

兼容写法说明 #

部分数据库支持更简洁的等价写法，比如SQL Server可以用ISNULL函数：

AND Tb.var1 = ISNULL(@var1, Tb.var1)
AND Tb.var2 = ISNULL(@var2, Tb.var2)

注意：如果你的表字段var1/var2本身允许为NULL，该写法会过滤掉字段值为NULL的行，此时优先使用前面的OR写法。

内容的提问来源于stack exchange，提问作者Gorodeckij Dimitrij