Luis Cazares (9/26/2016)
drew.allen (9/26/2016)
xr280xr (9/26/2016)
I don't specifically care about short circuiting. I just need to only evaluate b if a is false and only evaluate c if a and b are false. It's not a performance issue to me, it's the logic I need. I was only pointing out that short circuiting allows that to be written very neatly. I'm hoping for a way to write it more neatly in t-sql or confirmation that it can't be.A CASE expression will do this. It evaluates the subsequent conditions only if all of the preceding conditions evaluate to FALSE or UNKNOWN (NULL). The CASE expression CANNOT return a Boolean value, so generally you want to return some token (usually 'T', 'True' or 1) to indicate when the condition is true and then compare that token to the expected value.
That being said, be very sure that you really need to evaluate them in the specified order.
Drew
DON'T USE A CASE STATEMENT IN A WHERE CLAUSE. That's just a recipe for disaster, as your queries become non-SARGable. The CASE statement should go in the column list if you need to assign that row to a group. In the WHERE clause, you don't care if it doesn't short circuit the condition.
Never say, "Never." As with anything else, you have to evaluate it within the context, which we simply don't have. There may be, and likely are, more efficient solutions, but it doesn't appear that the OP is willing to give us enough information to suggest such an alternate. It's entirely possible that other criteria in the query will limit the rowset enough that the fact that this particular piece isn't SARGable may have minimal impact on query performance. We just don't know.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA