sql - T-SQL CASE WHEN 语句不适用于 between

Question

所以我坚持我的 where 语句。每个单独的案例在 where 子句中都运行良好，但是当我尝试将它包装在一个 case when 语句中时，它打破了我。在 case when 语句中使用 between 语句有问题吗？下面是代码。如果有任何想法，请分享!

英文的逻辑是ssrs表单上有一个复选框。所以 box 可以是 1 或 0 并且取决于我将执行 where 语句的值。

WHERE (CASE @box
WHEN 1 THEN (CheckReg.CheckDate BETWEEN @ChkStartDate and @ChkEndDate OR CheckReg.CheckDate IS NULL) 
ELSE 0 THEN (CheckReg.CheckDate BETWEEN @ChkStartDate and @ChkEndDate OR CheckReg.CheckDate IS NULL 
AND NOT  CheckReg.CheckDate IS NULL AND CheckReg.CheckDate IS NOT NULL)
END)

此外，如果有人知道更好的解决方案，那么我上面的逻辑请告诉我。我正在使用 Microsoft sql server

Answer 1

您需要使用OR

WHERE
  (@box = 1 AND (CheckReg.CheckDate BETWEEN @ChkStartDate and @ChkEndDate OR CheckReg.CheckDate IS NULL))
  OR
  (@box = 0 AND (CheckReg.CheckDate BETWEEN @ChkStartDate and @ChkEndDate OR CheckReg.CheckDate IS NULL 
                AND NOT CheckReg.CheckDate IS NULL AND CheckReg.CheckDate IS NOT NULL))

或者您可以在 WHEN 部分组合条件并与 THEN 进行比较:

WHERE 
   (CASE
     WHEN @box = 1 AND (CheckReg.CheckDate BETWEEN @ChkStartDate and @ChkEndDate OR CheckReg.CheckDate IS NULL)
     THEN 1
     WHEN @box = 0 AND (CheckReg.CheckDate BETWEEN @ChkStartDate and @ChkEndDate OR CheckReg.CheckDate IS NULL 
                        AND NOT CheckReg.CheckDate IS NULL AND CheckReg.CheckDate IS NOT NULL)
     THEN 1
     ELSE 0
   END
  ) = 1