Many times we would run into situation where user would want to select data limited by a criteria, or “all”. For example, let’s say we build a UI where user can list all the users by a certain State or all States.

Let’s say the State information is specified by the parameter @State, which can be a validate two character state abbreviation, or ‘ALL’
Most queries would be written as:
IF @State = 'ALL'
    SELECT * FROM User
    SELECT * FROM User WHERE State = @State
So far so good, huh? Well, there are a few issues with this approach
  1. Poor code reusability. The first part of SQL is redudant in both conditional branches (SELECT * FROM User). This is fine when the queries are simple. I have seen some complex SQL queries that are miles long, this means developers would have to copy the complex select statement a few places.
  2. With there is only one condition, we only need one branch. However, what if we have a complex query condition, such as gender (Male, Female or All), department (certain department, or all), status (full-time, part-time, contractor or all), etc … You can see the query can grow exponentially complicated. So in a case where customer requests an “Advanced Search” feature with 5 criteria, we can look at potentially 2 ^ 5 = 32 if branches for the SQL query.
There is a much simpler approach that would address the two problems above. It also improves the readability of your code as well.
Let’s take the first sample (by state), and rewrite it as follow
SELECT * FROM User WHERE (@State = ‘ALL’ OR State = @State)
That’s it! The @State = ‘ALL’ takes care of the ‘All’ situation! So, in a case where you want to take care other situations such as department, and status, your SQL query would look like
SELECT * FROM User WHERE (@State = ‘ALL’ OR State = @State)
  AND (@Department = ‘ALL’ OR Department = @Department)
  AND (@State = ‘ALL’ OR Status = @Status)
The number of AND/OR pairs is linearly equal to the number of criteria, not exponential!