SQL Case Statement Where? Use the "Dataman Maneuver"

Lets say you have an ice cream application. And you have two tables. One for storing the base ice cream and another for storing the actual flavors that are offered by the store. Your tables would have the following data:



Next, lets say that you have a dropdown for the base ice cream type which stores as its value the bid. In addition, one of the options is "ALL" with a value of "0." How would you write a query that will give you the rows from tblFlavors?

In VS.NET 2.0 you can add parameters to your selectcommand in the sqlDataSource control. Then, you can configure the parameter to get the value from your dropdown box. But, you have to do some work on the query to get this to work... Enter the Case statement in the where clause. Also known ast the "Dataman Maneuver" by certain trekky programmers with the initials of CRJones. :) You can visit Charley Jones at http://crjones.com


At first attempt you might try:
SELECT * FROM tblFlavors f
WHERE f.bid = @bid

This would work, but what would happen when you select the "All?" You wouldn't get any rows. You don't have any rows with a BID of 0. Enter the power of the Case statement in the where clause.

When you take a look at the where clause above, the WHERE clause is comparing the value of the bid for each row to the parameter @bid. This is a boolean operation. It is either true or false. All it cares about is whether the variables on both sides of the operator are the same datatype and that it can compare them. So, this allows us to do some crazy things with cases.

Lets try something else:
SELECT * FROM tblFlavors f
WHERE (Case when @Bid = 0 then 1 else 0 end) = 1

In this statement what are the two variables that are being compared? The right side is a 1, and the left side is whatever my case spits out. The database is smart enough to execute the case first and then compare the result against your 1. If you were to run your application, it would only work when you selected the "All" option. If you selected another option such as Vanilla, you would get nothing because @Bid would be = to 1 and in the above 1 isn't 0 so it returns 0. 0 doesn't = 1.

The solution is:
SELECT * FROM tblFlavors f
WHERE (Case when @Bid = 0 then 1
when @Bid = f.Bid then 1 else 0 end) = 1


Here it will check the first statement.
Does @Bid = 0?
yes: return the 1.
no: check the next one.
Does @Bid = f.Bid?
yes: return the 1.
no: go to else which returns 0.

Now, it checks the case value to 1 and if this is true you get your row.

Happy Coding