Case is not a control flow statement, it can't return portions of the query. It returns expressions (values)
Precisely! Thank you for calling attention to it. It was my thought to post a similar message as I was reading the thread. The terminology should be the teacher here.
Directly from Books Online article CASE (Transact-SQL) - SQL Server 2008 R2
(bold and italics added):
Evaluates a list of conditions and returns one of multiple possible result expressions.
The CASE expression has two formats:
- The simple CASE expression compares an expression to a set of simple expressions to determine the result.
- The searched CASE expression evaluates a set of Boolean expressions to determine the result.
You can compare the result of a CASE expression to a column or variable or the result of another expression (e.g. result of a call to a function
) but you cannot use a CASE expression to dictate the control flow, i.e. to directly influence the code that is executed.
There is a control flow construct in many classical programming languages called a switch statement
(a.k.a. switch/case statement
) which uses "case" as a keyword and that is where I think most of the confusion comes from, however there is no such construct in T-SQL.
__________________________________________________________________________________________________There are no special teachers of virtue, because virtue is taught by the whole community. --Plato