CASE (Transact SQL)

  • 0

CASE (Transact SQL)

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


Bookmark and Share