The 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).

CASE Syntax:

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

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
FROM students;

SELECT with a searched CASE expression

SELECT contract_id, "Amount Range" =
CASE
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'
END
FROM contracts
ORDER BY contract_id;