There’s a ton of articles out there on how to implement a case statement in a WHERE clause but couldn’t find one on how to implement a CASE statement in a WHERE clause that gives you the ability to use a NOT IN or IN filter. I guess the only way to explain this is to use an example, and I am fully aware that the use of this may not be the best practice and is most likely required because of poor database design/implementation but hey, when you inherit code, there’s really no other choice :)
Suppose I have a stored proc that has an optional value:
@OnlyNonExported bit = 0
I want to return all items from MYTRANSACTIONS table if @OnlyNonExported = 0, but if this value is 1 I want to return all items from MYTRANSACTIONS that have not been tracked in my TRACKEDTRANSACTIONS table. The original theory is to use a NOT IN clause to acheive the latter requirement:
SELECT * FROM mytransactions m
WHERE mytransactions.id NOT IN (SELECT id FROM trackedtransactions)
So if I wanted to use a case statement for this query, one would think you could do something like this:
SELECT * FROM mytransactions m
WHERE mytransactions.id NOT IN
CASE WHEN @OnlyNonExported = 0
THEN (SELECT -1)
ELSE (SELECT id FROM trackedtransactions)
END
But SQL doesn’t like this syntax and it turns out that you cannot use IN or NOT IN conditions with CASE statement in a WHERE clause, you can only use = or != conditions. So how do you achieve the above? Well the answer is even more dodgy that the above:
SELECT * FROM mytransactions m
WHERE mytransactions.id !=
CASE WHEN @OnlyNonExported = 0
THEN (SELECT -1)
ELSE COALESCE((SELECT id FROM trackedtransactions t WHERE t.id = m.id), -1)
END
So basically, when we want to return all transactions, return all rows where the id equals –1 (assuming that your IDs start at 1) and when we want to filter the results based on whether or not these IDs exist in another table, we only return rows who’s IDs don’t match the same ID in the tracked table. BUT if this ID doesn’t exist in the tracked table, then an empty result set is returned and the id won’t be matched against it, so we need a COALESCE function will will return a –1 value if there is an empty result set.
Hopefully you’ll never have to use this but if you do, hope this saves you some headaches :)