Sql case in where statement
Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.
In SQL, it's a Case Expression, *not* a Case Statement
sql "case statement"
returns 127,000 results. Meanwhile, if we do a search for the phrase
we get back only 43,900 results.
Why do I bring this up? Because that's a pretty good indicator that most people think the CASE construct in SQL is a statement that somehow alters code that is executed or compiled, when in fact it is really just an expression. that simply accepts and evaluates expressions as arguments and always simply returns a single value. That's it! In fact, a good case (pun intended) can be made that it is really just a fancy function with an odd, flexible syntax, and nothing else.
We see this a lot when people try to do things like this:
ORDER BY CASE WHEN @Sort=1 THEN Col1 ASC ELSE Col1 DESC END
And, of course, it doesn't work -- note the inclusion of the sort direction indicators (ASC/DESC) within the CASE parameters. And people wonder "why isn't the case statement [sic] working" ? They treat it almost as if it were some sort of run-time conditional compilation directive, that dynamically alters the code itself regardless of what commands are put within the case parameters, and the end result is an entirely new SQL command. That's not how it works! It is just a simple function, an expression, that returns a single value . And the arguments of the CASE, like any other function or expression, can also only be expressions. You cannot stuff random snippets of
T-SQL syntax in there, such as sort order indicators or references to database objects (i.e. table names), since those are not expressions that can be evaluated and returned as a single value.
I've talked a lot here about the difference between code and data, and this seems to be yet another place where the confusion surfaces. A CASE statement expression accepts and returns data, it doesn't accept and return code. It's really that simple.
It may help to imagine that CASE has a syntax that uses parenthesis, such as:
CASE ( WHEN. END )
which perhaps makes it more apparent that CASE is just a fancy function that returns a value. Unfortunately, that syntax is not valid; if it were, it might make code more readable -- especially for beginner -- and easier to visualize as a function or expression.
I'm sure some of the confusion comes from people with VB backgrounds, where CASE is indeed a statement, and segments of the CASE contain code that is executed, or not, depending on different conditions.
So, the next time you hear someone mention a "case statement" when discussing SQL -- stop them! That's right, just jump up in the middle of a meeting and yell "Noooo!!" and run across the room and throw hot coffee on their face! They might be a great SQL developer, and they might even personally understand exactly how a CASE works, but by using the phrase "case statement" it propagates the misunderstanding and the confusion about what CASE really is and what it does.
I hope I've made a compelling case. and just in case this doesn't make sense, the best case scenario might be to. ok, that's enough! Sorry! Case closed!
Print | posted on Thursday, May 03, 2007 9:15 AM | Filed Under [ Miscellaneous T-SQL ]Source: weblogs.sqlteam.com