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).
The basic syntax of a CASE expression in SQL Server is as follows:
WHEN condition1 THEN result1
WHEN condition2 THEN result2
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.
Here's an example that demonstrates the usage of the CASE expression in SQL Server:
WHEN UnitPrice <= 10 THEN 'Low'
WHEN UnitPrice > 10 AND UnitPrice <= 100 THEN 'Medium'
WHEN UnitPrice > 100 THEN 'High'
END AS PriceCategory
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
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;
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.