SQL Server中如何在WHERE子句中实现IN操作符的分支判断？

错误原因 #

你原有写法的核心问题是SQL中CASE属于值表达式，仅能返回具体的标量值，不能直接将IN这类布尔判断逻辑作为分支返回，因此语法不合法，无法执行。

正确实现方式 #

无需使用CASE表达式，直接通过OR拼接条件即可实现你预期的逻辑：当@CUSTOMER_NUMBER为NULL或空字符串时，跳过硬件编号匹配校验，否则要求tl.HARDWARE_NUMBER存在于临时表@HardwareNumberListTemp中。
修正后的完整SQL如下：

DECLARE @CUSTOMER_NUMBER varchar(8)
DECLARE @HardwareNumberListTemp TABLE (HNumber VARCHAR(19))
DECLARE @BEGIN_DATE datetime
DECLARE @END_DATE datetime

/* 此处为向@HardwareNumberListTemp插入数据的原有逻辑 */
SELECT  *   
FROM abc.tatatable tl WITH(nolock)
WHERE tl.INSERT_DATE BETWEEN @BEGIN_DATE AND @END_DATE
  AND (
    @CUSTOMER_NUMBER IS NULL 
    OR @CUSTOMER_NUMBER = ''
    OR tl.HARDWARE_NUMBER IN (SELECT t.HNumber FROM @HardwareNumberListTemp t)
  )

可选兼容写法（CASE表达式版本） #

如果你需要保留CASE表达式的写法，可将布尔判断转换为值匹配逻辑，写法如下：

DECLARE @CUSTOMER_NUMBER varchar(8)
DECLARE @HardwareNumberListTemp TABLE (HNumber VARCHAR(19))
DECLARE @BEGIN_DATE datetime
DECLARE @END_DATE datetime

/* 此处为向@HardwareNumberListTemp插入数据的原有逻辑 */
SELECT  *   
FROM abc.tatatable tl WITH(nolock)
WHERE tl.INSERT_DATE BETWEEN @BEGIN_DATE AND @END_DATE
  AND 1 = CASE
            WHEN @CUSTOMER_NUMBER IS NULL OR @CUSTOMER_NUMBER = '' THEN 1
            WHEN tl.HARDWARE_NUMBER IN (SELECT t.HNumber FROM @HardwareNumberListTemp t) THEN 1
            ELSE 0
          END

内容的提问来源于stack exchange，提问作者Soner from The Ottoman Empire