Decisions inside a query: CASE and IIF

CASE is SQL’s if-then-else. It is an expression: it produces one value per row, so it can go anywhere a value can: in SELECT, ORDER BY, WHERE, even inside other functions.

Searched CASE

The searched form tests a list of conditions. SQL Server tries them from the top and returns the result of the first one that is true:

1SELECT Name, Stock,2       CASE3           WHEN Stock = 0   THEN N'Out of stock'4           WHEN Stock < 20  THEN N'Low'5           WHEN Stock < 100 THEN N'OK'6           ELSE N'Plenty'7       END AS StockLevel8FROM dbo.Products9WHERE ProductId <= 6 OR Stock = 0;

Name              Stock  StockLevel
----------------  -----  ------------
Claw hammer          40  OK
Cordless drill       12  Low
Tape measure 8 m     65  OK
Cement 25 kg        300  Plenty
Sand 25 kg          220  Plenty
Timber 2x4 2.4 m    500  Plenty
Wall plugs (100)      0  Out of stock
Safety goggles        0  Out of stock

Reorder the WHENs and switch the ELSE off to see why the order matters:

Simple CASE

When every test compares the same expression with =, the simple form is shorter. It cannot test ranges or NULL (because = NULL is never true):

1SELECT OrderId, Status,2       CASE Status3           WHEN 'Shipped'   THEN N'On its way or delivered'4           WHEN 'Pending'   THEN N'Waiting to be packed'5           WHEN 'Cancelled' THEN N'Cancelled by customer'6           ELSE N'Check status'7       END AS ForCustomer8FROM dbo.Orders9WHERE OrderId IN (5, 6, 8);

OrderId  Status     ForCustomer
-------  ---------  -----------------------
      5  Shipped    On its way or delivered
      6  Cancelled  Cancelled by customer
      8  Pending    Waiting to be packed

CASE in ORDER BY: a custom sort

The warehouse wants pending orders first, then shipped, then cancelled. Alphabetical order would not give that, but a CASE that turns each status into a number does:

1SELECT OrderId, OrderDate, Status2FROM dbo.Orders3ORDER BY CASE Status WHEN 'Pending' THEN 1 WHEN 'Shipped' THEN 2 ELSE 3 END,4         OrderDate DESC;

OrderId  OrderDate   Status
-------  ----------  ---------
      9  2024-06-01  Pending
      8  2024-05-18  Pending
     10  2024-06-12  Shipped
      7  2024-05-05  Shipped
      5  2024-04-02  Shipped
      4  2024-03-15  Shipped
      3  2024-03-01  Shipped
      2  2024-02-10  Shipped
      1  2024-02-03  Shipped
      6  2024-04-20  Cancelled

One result type

All the results of a CASE must share one type. If they differ, SQL Server converts them all to the type with the highest precedence, and int beats text. Mixing a number and a word therefore fails as soon as the word comes up:

1SELECT Name,2       CASE WHEN Stock > 0 THEN Stock ELSE 'none' END AS StockShown3FROM dbo.Products;

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'none' to data type int.

Make every branch text instead: CASE WHEN Stock > 0 THEN CAST(Stock AS varchar(10)) ELSE 'none' END. Type precedence is the subject of the next lesson.

IIF and CHOOSE

IIF(test, ifTrue, ifFalse) is shorthand for a CASE with one WHEN and an ELSE. CHOOSE(n, a, b, c, …) picks the nth item of a list (counting from 1). Both are rewritten into CASE behind the scenes.

1SELECT Name, Price,2       IIF(Price >= 40, N'Big ticket', N'Everyday') AS Kind,3       CHOOSE(CategoryId, N'Tools', N'Building', N'Timber', N'Fixings', N'Paint', N'Safety') AS Category4FROM dbo.Products5WHERE ProductId IN (2, 5, 7, 11);

Name                 Price  Kind        Category
-------------------  -----  ----------  --------
Cordless drill       89.00  Big ticket  Tools
Sand 25 kg            4.10  Everyday    Building
Plywood sheet 18 mm  42.00  Big ticket  Timber
Paint roller set     12.50  Everyday    Paint

1SELECT Name, Price,2       CASE3           WHEN Price < 5  THEN N'Budget'4           WHEN Price < 20 THEN N'Standard'5           WHEN Price < 60 THEN N'Premium'6           ELSE N'Trade only'7       END AS Band8FROM dbo.Products9ORDER BY Price;

Name                 Price  Band
-------------------  -----  ----------
Sand 25 kg            4.10  Budget
Wall plugs (100)      4.50  Budget
Timber 2x4 2.4 m      6.80  Standard
Safety goggles        6.80  Standard
Cement 25 kg          7.20  Standard
Tape measure 8 m      9.75  Standard
Wood screws (200)    11.90  Standard
Paint roller set     12.50  Standard
Claw hammer          18.50  Standard
Plywood sheet 18 mm  42.00  Premium
Masonry paint 10 L   54.00  Premium
Cordless drill       89.00  Trade only