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

Question

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.

Answer 1

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

Answer 2

select *
from SomeTable t
where t.Column1 is null
and (@AdditionalFilter = 0 or t.Column2 is not null)

Answer 3

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.

Answer 4

This practice tends to confuse the query optimizer. I've seen SQL Server 2000 build the execution plan exactly the opposite way round and use an index on Column1 when the flag was set and vice-versa. SQL Server 2005 seemed to at least get the execution plan right on first compilation, but you then have a new problem. The system caches compiled execution plans and tries to reuse them. If you first use the query one way, it will still execute the query that way even if the extra parameter changes, and different indexes would be more appropriate.

You can force a stored procedure to be recompiled on this execution by using WITH RECOMPILE in the EXEC statement, or every time by specifying WITH RECOMPILE on the CREATE PROCEDURE statement. There will be a penalty as SQL Server re-parses and optimizes the query each time.

In general, if the form of your query is going to change, use dynamic SQL generation with parameters. SQL Server will also cache execution plans for parameterized queries and auto-parameterized queries (where it tries to deduce which arguments are parameters), and even regular queries, but it gives most weight to stored procedure execution plans, then parameterized, auto-parameterized and regular queries in that order. The higher the weight, the longer it can stay in RAM before the plan is discarded, if the server needs the memory for something else.