• The CASE expression has two formats:
  • The simple CASE expression compares an expression to a set of simple expressions to determine the result.
  • The searched CASE expression evaluates a set of Boolean expressions to determine the result.
    • It returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression.
• SQL Server allows for only 10 levels of nesting in CASE expressions.
• The CASE expression cannot be used to control the flow of execution of T-SQL statements, statement blocks, user-defined functions & stored procedures.
• The CASE statement evaluates its conditions sequentially and stops with the first condition whose condition is satisfied.
  • In some situations, an expression is evaluated before a CASE statement receives the results of the expression as its input.
  • Errors in evaluating these expressions are possible.
  • Aggregate expressions that appear in WHEN arguments to a CASE statement are evaluated first, then provided to the CASE statement.

Simple CASE Statement

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];

Searched CASE Statement

SELECT
	[ProductNumber],
	[PriceRange] = 
    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];

CASE in ORDER BY

SELECT 
	SalariedFlag, 
	BusinessEntityID
FROM HumanResources.Employee
ORDER BY 
	CASE [SalariedFlag]
		WHEN 1 THEN [BusinessEntityId]
		END DESC,
	CASE 
		WHEN [SalariedFlag] = 0 
			THEN [BusinessEntityId]
		END ASC;

Dynamic ORDER BY with CASE

DECLARE @OrderBy VARCHAR(10) = 'FirstName'
DECLARE @OrderByDirection VARCHAR(4) = 'DESC'

SELECT FirstName, LastName
FROM Person.Person
ORDER BY
	CASE WHEN @OrderBy = 'FirstName' AND @OrderByDirection = 'ASC' THEN FirstName END,
	CASE WHEN @OrderBy = 'FirstName' AND @OrderByDirection = 'DESC' THEN FirstName END DESC,
	CASE WHEN @OrderBy = 'LastName' AND @OrderByDirection = 'ASC' THEN FirstName END,
	CASE WHEN @OrderBy = 'LastName' AND @OrderByDirection = 'DESC' THEN FirstName END DESC

CASE in an UPDATE Statement

UPDATE HumanResources.Employee
SET [VacationHours] = 
    (CASE
			WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40
			ELSE VacationHours + 20.00
		END)
WHERE SalariedFlag = 0;

CASE in WHERE Clause

SELECT *
FROM HumanResources.Employee
WHERE [JobTitle] = 
	CASE 
		WHEN LEN([JobTitle]) < 10 THEN 'Chief Executive Officer'
		ELSE [JobTitle]
	END

CASE in a HAVING Clause

SELECT 
	[E].[JobTitle],
	[MaximumRate] = MAX([PH].[Rate])
FROM [HumanResources].[Employee] AS [E]
INNER JOIN [HumanResources].[EmployeePayHistory] AS [PH]
	ON [E].BusinessEntityID = [PH].BusinessEntityID
GROUP BY [JobTitle]
HAVING
-- If men, their rate must be above 40.00
MAX(CASE [E].[Gender] WHEN 'M' THEN [PH].[Rate] ELSE NULL END) > 40.00
OR
-- If female, their rate must be above 42.00
MAX(CASE [E].[Gender] WHEN 'F' THEN [PH].[Rate] ELSE NULL END) > 42.00
ORDER BY [MaximumRate] DESC;

IIF for Binary Decisions

SELECT IIF (-1 < 1, 1, 0);