SQL Server中Where子句内使用Case语句报错原因排查

错误核心原因 #

SQL的CASE是标量表达式，只能返回单个值（比如字符串、数字），不能直接返回ApprovalStatus <> 'Rejected'这种布尔判断逻辑。WHERE子句需要的是明确的布尔结果，你这样写相当于让CASE返回一个无法被WHERE识别的表达式，自然会报错。

另外还有两个语法小问题：

• 存储过程定义缺少AS关键字，正确写法是create procedure MyProcedure as
• SQL里字符串常量应该用单引号'，双引号"通常用来标识对象名（比如表、列），你写的data = "A"应该改成data = 'A'

修复方案 #

方案1：用AND/OR组合替代CASE（更高效，推荐）

把CASE的逻辑拆成普通的条件组合，SQL优化器更容易处理：

create procedure MyProcedure 
    @ApprovalFilter as nvarchar(50), 
    @BothPendingAndApproved as nvarchar(50)
AS
Select 
    * 
from 
    myTable
Where
    data = 'A'
    and Data2 = 'B'
    and (
        (@BothPendingAndApproved = 'Both' AND ApprovalStatus <> 'Rejected')
        OR
        (@BothPendingAndApproved = 'Specific' AND ApprovalStatus LIKE @ApprovalFilter)
    )

方案2：调整CASE返回可判断的标量值

让CASE返回1（符合条件）或0（不符合），再让WHERE判断结果是否等于1：

create procedure MyProcedure 
    @ApprovalFilter as nvarchar(50), 
    @BothPendingAndApproved as nvarchar(50)
AS
Select 
    * 
from 
    myTable
Where
    data = 'A'
    and Data2 = 'B'
    and CASE 
            WHEN @BothPendingAndApproved = 'Both' THEN 
                CASE WHEN ApprovalStatus <> 'Rejected' THEN 1 ELSE 0 END
            WHEN @BothPendingAndApproved = 'Specific' THEN 
                CASE WHEN ApprovalStatus LIKE @ApprovalFilter THEN 1 ELSE 0 END
        END = 1

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