Using the CASE Statement in SQL Server
In SQL, the CASE statement evaluates conditions and returns results using uses the WHEN…THEN expressions in the following way:
- Checks if the condition specified by WHEN is met
- Returns the result specified by THEN
The CASE rules are:
- At least one WHEN… THEN expression is obligatory
- The END keyword must finalize the CASE statement
- The ELSE argument is optional — CASE will return the result specified by it if no conditions are TRUE
- No more than 10 levels of nesting for SQL Server are allowed
There are two types of SQL CASE statements:
- Simple CASE statement defines the result value by evaluating an expression against multiple values.
- Searched CASE statement defines the result value by evaluating a set of Boolean expressions.
The basic syntax for the SELECT statement with a simple CASE expression is:
SELECT CASE expression
WHEN condition1 THEN result1
WHEN condition2 THEN result2
…
ELSE result
END
Overall, CASE can be in any statement or clause. Let’s review the most common examples.
SQL CASE statement with multiple conditions
We can add multiple conditions to the CASE statement and combine them with the AND operator:
CASE expression
WHEN condition1 AND condition2 THEN result1
ELSE result2
END
Also, we can use several WHEN/THEN expressions:
CASE WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN condition3 THEN result3
…
ELSE result N
END
Note: Multiple conditions in CASE get processed one by one, and the statement stops checking them after the first successful condition.
CASE with the ORDER BY clause
We can apply CASE to change the output order and instruct how to sort the results according to the requirements.
SELECT * FROM table_name
ORDER BY
CASE expression
WHEN condition1 THEN result1,
WHEN condition2 THEN result2
…
ELSE result N
END
CASE with the GROUP BY clause
The CASE expression allows for arranging the query output in the required way:
SELECT
CASE WHEN condition1 THEN result1
ELSE result2 END AS condition,
COUNT(1) AS count
FROM table_name
GROUP BY CASE WHEN condition1 THEN result1
ELSE result2
END
CASE with INSERT and UPDATE
We can use CASE in the basic INSERT and UPDATE operations to make them more precise.
In the INSERT statement, CASE allows for adding values according to specific conditions:
INSERT INTO column_name
VALUES (CASE expression
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result3
END);
In the UPDATE statement, CASE allows us to select or set a new value according to the specified conditions:
UPDATE table_name
SET column_value =
CASE WHEN condition1 THEN result1,
WHEN condition2 THEN result2
ELSE result3
END
Using CASE WHEN with SUM()
The CASE WHEN expression with the SUM() function will assign values by specific conditions and sum them correctly.
SELECT COUNT(*) AS TotalCount,
SUM(CASE WHEN condition1
THEN result1
ELSE result3
END) AS column_name1,
SUM(CASE WHEN condition1
THEN result2
ELSE result3
END) AS column_name2
FROM table_name
Conclusion
The CASE statement is helpful for many operations in SQL Server. It makes queries more versatile and helps bring more precise results. It is worth applying SQL CASE in your work.
