|Programming in MS SQL Server 2012|
CASE ... WHEN ... THEN Statement
CASE ... WHEN ... THEN statement is used to evaluates a list of conditions and returns one of multiple possible result expressions. Simple CASE expression compares an expression to a set of expressions to determine the result. Search CASE expression evaluates a set of Boolean expression to determine the result. CASE statement provides alternative by using the last statement like ELSE.
CASE can use in SELECT, UPDATE, DELETE and SET statements and IN, WHERE, ORDER BY and HAVING clauses.
Input_express is used to evaluated when the simple CASE format is used input_expression is any valid expression.
WHEN when_expression is a simple expression to which input_expression is compared when the simple CASE format is used when_expression is any valid expression. The data types of input_expression and each when_expression must be the same or must be an implicit conversion.
THEN result_expression is used to returned when input_expression equals when_expression evaluates to TRUE, or Boolean_expression evaluates to TRUE result expression is any valid expression.
ELSE else_result_expression is used to returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE return NULL else_result_expression is any valid expression. The data type of else_result_expression and any result_expression must be the same or must be an implicit conversion.
WHEN Boolean_expression is used to evaluated when using the searched CASE format. Boolean_expression is any valid Boolean expression.
The following example describes simple CASE ... WHEN ... ELSE ... END statement.
Query Output Screen
* * * * *