Suppose we want to show the availability level for each product in a report, but we don't want to simply print the
UnitsInStock column. The column contains a precise number of units available, but we only need an overall level, like
N'high'. How can we do this? Take a look:
WHEN UnitsInStock > 100 THEN N'high'
WHEN UnitsInStock > 50 THEN N'moderate'
WHEN UnitsInStock > 0 THEN N'low'
WHEN UnitsInStock = 0 THEN N'none'
END AS Availability
We create a brand-new column named
Availability. You can see that we use a special construction called
CASE WHEN. It returns different values based on the conditions you specify.
How do we build a
CASE WHEN? We start with the word
CASE, followed by one or more
WHEN ... THEN ... conditions. After the list of conditions, we add the
END keyword to indicate the end of the construction.
CASE WHEN looks for the first condition satisfied in the
WHEN part and takes its value from the
In the query above,
CASE WHEN checks the value of the
UnitsInStock column. If it's greater than
Availability column gets the value
N'high'. If it's not greater than
100, but is greater than
50, the column value is
N'moderate'. And when it's less than
50, its value is
N'low'. Zero units in stock, of course, gets a value of