The stored procedure bit parameter activates the optional where clause to check for null

Question

I have a stored procedure that looks like this:

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 does not work. How can I activate an optional where clause that checks 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 (@AdditionalFilter = 0 OR T.Column2 IS NOT NULL) 

If @AdditionalFilter is 0, the column will not be evaluated, since it cannot affect the result of the part between the brackets. If it is nothing but 0, the column condition will be evaluated.

Answer 2

This practice tends to confuse the query optimizer. I saw SQL Server 2000 build the execution plan in reverse and use the index in column 1 when the flag is set and vice versa. SQL Server 2005 seemed to at least get an execution plan right away when it was first compiled, but you had a new problem. The system caches compiled execution plans and tries to reuse them. If you first use the query in one direction, it will still execute the query this way, even if the additional parameter changes and other indexes are more suitable.

You can force recompilation of the stored procedure during this execution with WITH RECOMPILE in the EXEC statement or each time by specifying WITH RECOMPILE in the CREATE PROCEDURE . There will be a penalty, as SQL Server re-analyzes and optimizes the query each time.

In general, if your request form changes, use dynamic SQL generation with parameters . SQL Server also caches execution plans for parameterized queries and automatically parameterized queries (where it tries to determine which arguments are parameters) and even regular queries, but it gives the most weight to the stored procedure execution plans, then parameterized, automatically parameterized and regular queries in this okay. The higher the weight, the longer it can remain in RAM until the plan is discarded if the server needs memory for something else.

Answer 3

 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 4

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