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'low' or N'high'. How can we do this? Take a look:

SELECT
  ProductID,
  ProductName,
  UnitsInStock,
  CASE
    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
FROM Products;

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 THEN part.

In the query above, CASE WHEN checks the value of the UnitsInStock column. If it's greater than 100, the 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 N'none'.