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;
Visual Expert 2024
 VETSQLRULE62