Friday, October 9, 2009

Case statement with multiple conditions in Where clause


In some situations you need to filter data from a table by using CASE
statement.
Here is an example of a CASE statement with multiple conditions/checks
in the WHERE clause.

Suppose you have the following table:






DECLARE @SomeFilter CHAR(1)SET @SomeFilter='Y'
SELECT *
FROM Customers
WHERE (CASE WHEN @SomeFilter='Y' AND VIPLevel>2 AND Age>20 THEN 1
                        WHEN @SomeFilter='N' AND CustomerID>2 AND VIPLevel>3 THEN 1
              ELSE 0 END )=1

1 comment:

  1. Waiting for further posts.
    Hope you aren’t working too hard with multiple conditions in WHERE clause...

    ReplyDelete