CASE expression is used in SQL Server to evaluate a list of conditions and return one of
several possible result expressions.
The CASE expression evaluates the conditions sequentially and returns the result of the first condition whose condition is met.
In SQL Server, the CASE expression can be used in statements (SELECT, UPDATE, DELETE and SET) and
in clauses (IN, WHERE, ORDER BY and HAVING).
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
Simple CASE Example:
SELECT name, city,
CASE WHEN city = 'New York' THEN 'NY'
WHEN city = 'San Antonio' THEN 'SA'
ELSE NULL END AS short_city_name
SELECT with a searched CASE expression
SELECT contract_id, "Amount Range" =
WHEN amount between '0' and '500' THEN 'Limit 500'
WHEN amount between '500' and '1000' THEN 'Limit 1000'
WHEN amount between '1000' and '2000' THEN 'Limit 2000'
ELSE 'Over Limit 2000'
ORDER BY contract_id;