CASE expressions

This lesson uses the unmodified DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Choose a result value from ordered conditions without changing stored rows.

Run the example

SELECT OrderID, OrderStatus,
       CASE WHEN OrderStatus IN (N'Shipped', N'Completed')
            THEN N'Final' ELSE N'Open' END AS StatusGroup
FROM dbo.Orders
WHERE OrderID <= 4
ORDER BY OrderID;

Read it step by step

1. WHERE restricts the example to four known orders.
2. CASE tests the final statuses first; ELSE labels every other status Open.
3. CASE returns a value per row. It does not update OrderStatus.

Check the result

The result includes all four selected orders in OrderID order.

Practice

Label Products 1, 2, and 4 as Premium when ListPrice is at least 100, otherwise Standard.

SELECT ProductID, ListPrice,
       CASE WHEN ListPrice >= 100 THEN N'Premium'
            ELSE N'Standard' END AS PriceBand
FROM dbo.Products
WHERE ProductID IN (1, 2, 4)
ORDER BY ProductID;