It is common knowledge that you can apply a Case statement in the SELECT or ORDER BY portion of a SQL statement. What isn’t well known is that you can use it effectively in a WHERE clause.

Take an example I just ran into:

I want to show all orders to every employee unless the order is assigned to employeeid 263, EXCEPT if the person trying to view all orders IS employeeid 263 or if it is that persons boss. In that case, show every order.

This appears really simple and could be handled in a where clause, but I couldn’t figure it out. And even if there is a better way, why not use a CASE statement just for fun anyway:

select * from Orders
where
case
when @loginid = 263 or @loginid = 181
then employeeid
when (@loginid <> 263 and @loginid <> 181) and employeeid = 263
then 0
else employeeid
end
= employeeid

The first when says “if you are #263 or #181, set employeeid = employeeid, which will always be true and will the order.

The second when says, “if you aren’t #263 or #181, and the current order is assigned to #263, then set t.employeeid = 0” which will always be false, and never show the order.

The tricky part of adapting the Case statement is realizing you are only setting one side of an equation, and the second part, the “= employeeid” follows after the “end”.