case expression simplifies many conditional Transact-SQL constructs. Instead of using a series of if statements, case expression allows you to use a series of conditions that return the appropriate values when the conditions are met. case expression is ANSI-SQL-compliant.
With case expression, you can:
Simplify queries and write more efficient code
Convert data between the formats used in the database (such as int) and the format used in an application (such as char)
Return the first non-null value in a list of columns
Write queries that avoid division by 0
case does not avoid division by 0
errors if the divisor evaluates to a constant expression, because
Adaptive Server evaluates constant expressions before executing
the case logic. This sometimes causes division
by zero.
To work around:
Use nullif(). For example:
(x/nullif(@foo.0)0
Include column values so that they cancel each other, forcing Adaptive Server to evaluate the expression for each row. For example:
(x/(@foo + (col1 - col1))
Compare two values and return the first value if the values do not match, or a null value if the values do match
case expression includes the keywords case, when, then, coalesce, and nullif. coalesce and nullif are an abbreviated form of case expression. See the Reference Manual: Commands.
