Stored procedure bit parameter activating additional where clause to check for null

sql, stored-procedures

Solution

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)

If @AdditionalFilter is 0, the column won't be evaluated since it can't affect the outcome of the part between brackets. If it's anything other than 0, the column condition will be evaluated.

Problem

I have a stored procedure that looks like: ``` 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 ``` Needless to say, this doesn't work. How can I activate the additional where clause that checks for the @AdditionalFilter parameter? Thanks for any help.

Original source