Evaluates a list of conditions and returns one of multiple possible result expressions.
CASE has two formats:
· The simple CASE function compares an expression to a set of simple expressions to determine the result.
· The searched CASE function evaluates a set of Boolean expressions to determine the result.
Examples:
A. Using a SELECT statement with a simple CASE function
Within a SELECT statement, a simple CASE function allows for only an equality check; no other comparisons are made. The following example uses the CASE function to change the display of product line categories to make them more understandable.
USE AdventureWorks;
GO
SELECT ProductNumber, Category =
CASE ProductLine
WHEN ‘R’ THEN ‘Road’
WHEN ‘M’ THEN ‘Mountain’
WHEN ‘T’ THEN ‘Touring’
WHEN ‘S’ THEN ‘Other sale items’
ELSE ‘Not for sale’
END,
Name
FROM Production.Product
ORDER BY ProductNumber;
GO
B. Using a SELECT statement with a searched CASE function
Within a SELECT statement, the searched CASE function allows for values to be replaced in the result set based on comparison values. The following example displays the list price as a text comment based on the price range for a product.
USE AdventureWorks;
GO
SELECT ProductNumber, Name, ‘Price Range’ =
CASE
WHEN ListPrice = 0 THEN ‘Mfg item – not for resale’
WHEN ListPrice < 50 THEN ‘Under $50’
WHEN ListPrice >= 50 and ListPrice < 250 THEN ‘Under $250’
WHEN ListPrice >= 250 and ListPrice < 1000 THEN ‘Under $1000’
ELSE ‘Over $1000’
END
FROM Production.Product
ORDER BY ProductNumber ;
GO
