sql - 存储过程位参数激活附加 where 子句以检查空值

Question

我有一个看起来像的存储过程:

CREATE PROCEDURE dbo.usp_TestFilter
  @AdditionalFilter BIT = 1
AS
  SELECT *
  FROM dbo.SomeTable T
  WHERE
    T.Column1 IS NOT NULL
    AND CASE WHEN @AdditionalFilter = 1 THEN
      T.Column2 IS NOT NULL

不用说，这行不通。如何激活检查 @AdditionalFilter 参数的附加 where 子句？谢谢你的帮助。

Answer 1

CREATE PROCEDURE dbo.usp_TestFilter
  @AdditionalFilter BIT = 1
AS
  SELECT *
  FROM dbo.SomeTable T
  WHERE
    T.Column1 IS NOT NULL
    AND (@AdditionalFilter = 0 OR
      T.Column2 IS NOT NULL)

如果@AdditionalFilter 为 0，则不会评估该列，因为它不会影响括号之间部分的结果。如果它不是 0，则将评估列条件。