I came across a scenario where I needed to write conditional statements in my WHERE clause of my SQL that was more complex than what a ‘CASE WHEN’ statement could provide.
I have two parameters, @FieldA and @FieldB. Both parameters can have a value of either ‘A’, ‘Y’, or ‘N’.
If the value = ‘A’, I want to basically bring back all records as if I am not using a filter at all as if to say where the column on the table >=0.
If the value = ‘Y’, I want to bring back rows where the column on the table > 0.
If the value = ‘N’, I want to bring back rows where the column on the table = 0.
Here is my table:
Running the following SELECT statements (without using the parameters mentioned above) brings back these result counts.
Now, I need to build one SQL statement using the the two parameters mentioned above to yield the same results.
Instead of using a ‘CASE WHEN’ statement, you simply examine your parameter in your WHERE clause, and depending on parameter value, you follow the evaluation with the ‘AND’ operand and the fully qualified filtered statement that includes the database column, the operator, and the value to compare.
SELECT FieldA, FieldB FROM test WHERE (((@FieldA = 'Y' and Fielda > 0) OR (@FieldA = 'N' and Fielda = 0) OR (@FieldA = 'A' and Fielda >= 0)) AND ((@FieldB = 'Y' and FieldB > 0) OR (@FieldB = 'N' and FieldB = 0) OR (@FieldB = 'A' and FieldB >= 0)))
As you can see from the above statement, the outcome of the WHERE clause would look something like this:
SELECT FieldA, FieldB FROM test WHERE Fielda ? 0 AND FieldB ? 0
The ? would be translated to be either ‘>’, ‘=’, or ‘>=’ depending on the value of your parameters.
So as you can see, ‘CASE WHEN’ can be very limited where the above solution allows you to fully qualify your conditional statements.