SQL Server中如何根据变量值启用/禁用WHERE子句筛选块（免拼接）

我有一个较长的SQL查询，核心部分为WHERE子句。需求是通过预先声明的变量来启用或禁用其中的筛选片段，以此避免使用字符串拼接构造查询。

我的尝试代码如下：

DECLARE @previousReadingEqualsZero AS BIT = 0
DECLARE @previousReadingBiggerThanLastReading AS BIT = 0
DECLARE @readingIncidentIds AS TABLE
(
    Id UNIQUEIDENTIFIER
)

-- 大量CTE和JOIN逻辑...

WHERE (CASE
           WHEN @previousReadingEqualsZero = 1 THEN CTE_PreviousReadings.Consumption = 0
           ELSE TRUE END)
  AND (CASE
           WHEN @previousReadingBiggerThanLastReading = 1
               THEN CTE_PreviousReadings.Consumption > CTE_MostRecentReadings.Consumption
           ELSE TRUE END)
  AND (CASE
           WHEN EXISTS(SELECT Id FROM @readingIncidentIds) = TRUE
               THEN CTE_MostRecentReadingIncidents.Id IN (@readingIncidentIds)
           ELSE TRUE END)

但SQL Server报错，错误信息为：

Msg 102, Level 15, State 1, Line 187 Incorrect syntax near '='. Msg
102, Level 15, State 1, Line 194 Incorrect syntax near '='.

请问我的实现方式存在什么问题？是否有无需字符串拼接即可实现需求的方法？

问题根源 #

你的写法错误在于CASE表达式的语法使用不当。SQL中的CASE表达式是返回具体值（字符串、数字、布尔值等）的表达式，不能直接在THEN分支中写入条件判断语句（比如CTE_PreviousReadings.Consumption = 0这类比较式不能作为CASE的返回结果）。

无需字符串拼接的正确实现 #

可以直接用逻辑运算符（OR/AND）实现“开关式”筛选：当控制变量为0时，让该条件分支恒成立（相当于禁用筛选）；当变量为1时，才启用对应的筛选规则。同时注意表变量在IN子句中的正确用法（需要通过子查询读取表变量内容）。

修改后的WHERE子句如下：

WHERE 
    -- 控制是否启用“上一次读数为0”的筛选
    (@previousReadingEqualsZero = 0 OR CTE_PreviousReadings.Consumption = 0)
    -- 控制是否启用“上一次读数大于最新读数”的筛选
    AND (@previousReadingBiggerThanLastReading = 0 OR CTE_PreviousReadings.Consumption > CTE_MostRecentReadings.Consumption)
    -- 控制是否启用“关联指定事件ID”的筛选（表变量为空时禁用）
    AND (NOT EXISTS(SELECT Id FROM @readingIncidentIds) OR CTE_MostRecentReadingIncidents.Id IN (SELECT Id FROM @readingIncidentIds))

补充说明 #

• 这种写法保持了查询的静态结构，避免了字符串拼接带来的SQL注入风险和维护麻烦。
• 如果查询在不同变量值下执行计划差异较大导致性能问题，可以在查询末尾添加OPTION(RECOMPILE)，让SQL Server根据当前变量值生成最优执行计划。

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