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

Syntax

The basic syntax of a CASE expression in SQL Server is as follows:

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE resultN
END

Here's a breakdown of each component:

CASE: This keyword initiates the CASE expression.
WHEN condition THEN result: This component evaluates the specified condition and returns the corresponding result if the condition is true.
ELSE result: This component specifies the result to be returned if none of the previous conditions evaluate to true.
END: This keyword marks the end of the CASE expression.

You can have multiple WHEN conditions, allowing you to perform complex conditional logic. The conditions are evaluated sequentially, and the first condition that evaluates to true determines the result returned. If none of the conditions are true, the ELSE result (if provided) is returned. If there is no ELSE result and no conditions evaluate to true, the CASE expression returns NULL.

Example

Here's an example that demonstrates the usage of the CASE expression in SQL Server:

SELECT ProductName,
UnitPrice,
CASE
WHEN UnitPrice <= 10 THEN 'Low'
WHEN UnitPrice > 10 AND UnitPrice <= 100 THEN 'Medium'
WHEN UnitPrice > 100 THEN 'High'
ELSE 'Unknown'
END AS PriceCategory
FROM Products;

In this example, we have a table called "Products" with columns for "ProductName" and "UnitPrice." The CASE expression is used to create a new column called "PriceCategory," which categorizes the products based on their unit price. If the unit price is less than or equal to 10, it is categorized as 'Low.' If the unit price is between 10 and 100 (inclusive), it is categorized as 'Medium.' If the unit price is greater than 100, it is categorized as 'High.' If none of the conditions match, the category is set to 'Unknown.'

The resulting query output will include the original columns along with the computed "PriceCategory" column.

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;

The CASE expression in SQL Server is not limited to simple comparisons. You can use complex conditions, subqueries, and even nest CASE expressions within each other to achieve more advanced logic and transformations in your queries.

Overall, the CASE expression is a versatile feature of SQL Server that enables you to perform conditional operations and customize the output of your queries based on specific conditions.