Lesson 11. Displaying Data Based On Conditions (Case Statement)

Sometimes the data you want to output will depend on data in other columns. You can do that with a CASE statement in your SELECT clause.

The CASE statement comes in two flavors, but my personal preference is to always use what is called the searched CASE expression.

SQL Server evaluates the CASE statement in a lazy way. It does not check all conditions. It starts from the first condition and works its way down until a condition evaluates to true.

CASE Syntax

CASE
WHEN [boolean expression] THEN [display this] --you can have as many of these as necessary
ELSE [display this] --when no conditions are met, display this
END

Examples

Two Examples Of CASE Statements

USE AdventureWorks2016

SELECT 
ProductNumber,
Name AS ProductName,
Color,
CASE
WHEN Color IS NOT NULL THEN Color
ELSE 'Color Is Not Avalable'
END AS ProductColor
FROM Production.Product

SELECT 
SalesOrderDetailID,
OrderQty,
LineTotal,
CASE
WHEN LineTotal BETWEEN 0 AND 1000 THEN 'These guys are cheap.'
WHEN LineTotal BETWEEN 1000 AND 20000 THEN 'These guys are alright.'
WHEN LineTotal > 20000 THEN 'These guys are big spenders'
ELSE 'Who the heck are these guys'
END AS CommentsFromTheCMO
FROM Sales.SalesOrderDetail