Home
CASE expressions should end with ELSE clauses
Description
The "CASE expressions should end with ELSE clauses" rule states that all CASE expressions used in SQL Server code should include an ELSE clause. This ensures that all possible values are accounted for, and helps to prevent unexpected results. The ELSE clause should include an appropriate action, such as setting a value to NULL, or returning a default value. This helps to ensure that all possible cases are handled in a consistent manner, and that no unexpected results occur.
Key Benefits
- Ensures code safety: CASE expressions should always end with ELSE clauses to ensure that any unexpected input is handled correctly.
- Provides clarity: By including an ELSE clause, the logic of the CASE expression is more clearly defined and easier to understand.
- Reduces complexity: ELSE clauses can help to reduce the complexity of the CASE expression by providing a single point of handling for unexpected values.
Non-compliant Code Example
SELECT OrderID, Quantity,
CASE
WHEN Quantity > 30 THEN 'The quantity is greater than 30'
WHEN Quantity = 30 THEN 'The quantity is 30'
END AS QuantityText --Non compliant code
FROM OrderDetails;
Compliant Code Example
SELECT OrderID, Quantity,
CASE
WHEN Quantity > 30 THEN 'The quantity is greater than 30'
WHEN Quantity = 30 THEN 'The quantity is 30'
ELSE 'The quantity is under 30' --Compliant code
END AS QuantityText
FROM OrderDetails;