如何用@BOOL_HAS_INSPECTION参数实现T-SQL WHERE子句的NULL判断？

你的写法错误是因为CASE语句无法直接返回IS NOT NULL/IS NULL这类条件关键字，正确的做法是通过逻辑表达式组合来实现参数控制：

方法一：逻辑与或组合（推荐） #

这种写法可读性强，性能也更优：

AND (
    (@BOOL_HAS_INSPECTION = 1 AND PTI.UID_PTINSPECTION IS NOT NULL)
    OR (@BOOL_HAS_INSPECTION = 0 AND PTI.UID_PTINSPECTION IS NULL)
)

逻辑清晰：当参数为1时，强制检查字段非空；参数为0时，强制检查字段为空。

方法二：借助CASE返回布尔等价结果 #

如果偏好使用CASE，可以让它返回1/0代表布尔结果，再与1比较：

AND (
    CASE 
        WHEN @BOOL_HAS_INSPECTION = 1 THEN IIF(PTI.UID_PTINSPECTION IS NOT NULL, 1, 0)
        ELSE IIF(PTI.UID_PTINSPECTION IS NULL, 1, 0)
    END = 1
)

同理，对于@BOOL_HAS_DEFECT控制TMD.UID_TMDEFECT的判断，也可以套用同样逻辑：

AND (
    (@BOOL_HAS_DEFECT = 1 AND TMD.UID_TMDEFECT IS NOT NULL)
    OR (@BOOL_HAS_DEFECT = 0 AND TMD.UID_TMDEFECT IS NULL)
)

完整WHERE子句示例 #

WHERE ... other conditions ...
  AND (
      (@BOOL_HAS_INSPECTION = 1 AND PTI.UID_PTINSPECTION IS NOT NULL)
      OR (@BOOL_HAS_INSPECTION = 0 AND PTI.UID_PTINSPECTION IS NULL)
  )
  AND (
      (@BOOL_HAS_DEFECT = 1 AND TMD.UID_TMDEFECT IS NOT NULL)
      OR (@BOOL_HAS_DEFECT = 0 AND TMD.UID_TMDEFECT IS NULL)
  )

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