SQL - CASE WHEN THEN ELSE END

T-SQL CASE check conditions and returns result expressions if it find the first
condition logical true value.

Two version of CASE is available:

1- Searched CASE expression:

CASE
WHEN Boolean_expression THEN result_expression [..n]
[ ELSE else_result_expression ]
END

- For each WHEN in CASE expression it evaluates the conditional expresiion in front of WHEN
- It return the first result_expression of the first TRUE conditional expression in order.
- If no conditional expression is TRUE, then it returns else_result_expression if an ELSE clause is specified.
- If no conditional expression is TRUE and there is no ELSE clause, it returns a NULL value.

Example:

use AdventureWorksLT
SELECT [name], [listprice],
CASE
WHEN [color] = 'Black' THEN [listprice] * 0.9
WHEN [color] = 'Red' THEN [listprice] * 0.45
WHEN [color] = 'White' THEN [listprice] * 0.6
ELSE
[listprice]
END as discountPrice
FROM [AdventureWorksLT].[SalesLT].[Product]

2- The simple CASE expression:
Operates by comparing the first expression to the expression in each WHEN clause.
If equal, the expression in the THEN clause will be returned.

CASE input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END

- It evaluates input_expression, and in the order specified for each WHEN clause
- It checks to see if when_expression is equal to input_expression.
- It returns the result_expression of the first match as explained above.
- If no input_expression equals to when_expression, it returns the else_result_expression if an ELSE clause is exist.
- If no input_expression equals to when_expression and ELSE clause is not exist, it returns a NULL value.

Example:

SELECT [name], [listprice],
CASE [color]
WHEN 'Black' THEN 'In stock'
WHEN 'Red' THEN 'Sold out'
WHEN 'White' THEN 'On web'
WHEN 'Blue' THEN 'In order'
ELSE
'CALL'
END as availability
FROM [AdventureWorksLT].[SalesLT].[Product]

Note:
CASE in both formats support an optional ELSE argument.
CASE can be used in any statement or clause that allows a valid expression,
such as SELECT, UPDATE, DELETE and SET, and also in IN, WHERE, ORDER BY, and ...

enjoy using CASE !

SQL - CASE WHEN THEN ELSE END