As some of you may know, I recommend to avoid using CASE expressions in the WHERE clause of a query to express conditional logic. I prefer to have everything translated to simple ANDs, ORs and NOTs to keep things a) portable, b) easier to read and c) efficient.
Learning some good boolean logic techniques will go a long way towards making your queries more efficient, and you won't need to rely on CASE's and other methods of doing conditional logic.
First off, when I say “conditional logic”, I am talking about something like this:
“If A then B”
where A and B are both conditions. For example, in a WHERE clause, you might want to implement a condition like this:
“If (@ReturnAll <>1) THEN (EmpID = @EmpID)”
In other words, if the @ReturnAll parameter is 1, then return all of the rows, but if @ReturnAll is not 1, then only return rows where EmpID is equal to the @EmpID parameter supplied.
To express this logic in the WHERE clause, many people might code it like this:
WHERE EmpID = CASE WHEN @ReturnAll<>1 THEN @EmpID ELSE EmpID END
However, this is kind of counter-intuitive (why should we check that EmpID = EmpID ?) and can be really tough to implement when the condition spans more than 1 column in the table (you need multiple CASE's). Also, if EmpID is null this will fail.
The alternative is to translate the condition into a regular boolean expression using only AND, OR and NOT. The logical translation of “IF A then B” is:
“(Not A) or B”
If you work it out on paper, you will see it makes sense. To translate our WHERE clause requirement using the above logic, it becomes:
WHERE (@ReturnAll =1) OR (EmpID = @EmpID)
We are saying if @ReturnAll is 1, don't bother evaluating more of the condition -- return the row because the expression is TRUE. But if @ReturnAll <> 1, then EmpID must be equal to @EmpID for the condition to be true and the row to be returned. Exactly the conditional logic we wish to express.
To show another example, suppose we wish to say:
“IF @Filter=1 THEN Date= @Date and Cust= @Cust and Emp= @Emp”
Expressing this in a CASE clause results in:
WHERE Date = CASE WHEN @Filter=1 THEN @Date ELSE Date END AND
Cust = CASE WHEN @Filter=1 THEN @Cust ELSE Cust END AND
Emp = CASE WHEN @Filter=1 THEN @Emp ELSE Emp END
A little hard to read and quite inefficient -- all 3 case expressions must be evaluated for each row in the result set. Without CASE, we get:
WHERE @Filter<>1 OR (Date= @Date and Cust= @Cust and Emp= @Emp)
Much easier to read and maintain, and faster -- if @Filter <>1, the rest of the expression can be ignored by the optimizer.
Another common situation is using a single variable to implement the optional filter. For example:
"IF @CustID is not null THEN CustID = @CustID"
This is often implemented using ISNULL() or COALESCE() like this:
WHERE CustID = ISNULL(@CustID, CustID)
This is basically the same as writing a CASE expression in that it will not use an index on our column and doesn't implement solid boolean logic.
Converting that IF to a simple boolean expression results in a nice WHERE clause of:
WHERE (@CustID is null OR CustID = @CustID)
which, again, is the preferred way to implement this type of logic in SQL. It is short, simple, portable, easy to read and maintain, and efficient.
Finally, to express:
IF A THEN B ELSE C
you would write it as:
((Not A) or B) AND (A or C)
a little harder, but it does the job! No need for CASE in the WHERE clause ... trust me !
(of course, you may need CASE to manipulate some columns or expressions in the WHERE, just don't use it for boolean logic).
UPDATE: Much more on optimizing your conditional WHERE clauses here, including implementing more complex conditional parameters.