Home

Description

The "CASE expressions should not have too many WHEN clauses" rule states that when using CASE expressions in SQL Server, the number of WHEN clauses should be limited to improve readability and maintainability of the code. Having too many WHEN clauses can make the code difficult to read and understand, and can also lead to potential performance issues. This rule encourages developers to use simpler alternatives such as IF/ELSE or WHILE loops when multiple WHEN clauses are needed. Additionally, it is recommended to use meaningful names for each WHEN clause to make the code easier to read and maintain.

Key Benefits

• Increased readability: CASE expressions should not have too many WHEN clauses to ensure code is more easily understood.
• Improved performance: Fewer WHEN clauses allow the database to process the expression faster.
• Reduced complexity: Having fewer WHEN clauses simplifies the expression, making it easier to maintain.

Non-compliant Code Example

SELECT 
CASE
    WHEN UnitsInStock < 10 THEN 'The quantity is less than 10' 
    WHEN UnitsInStock < 20 THEN 'The quantity is less than 20' 
    WHEN UnitsInStock < 30 THEN 'The quantity is less than 30' 
    WHEN UnitsInStock < 40 THEN 'The quantity is less than 40' 
    WHEN UnitsInStock < 50 THEN 'The quantity is less than 50' 
    WHEN UnitsInStock < 60 THEN 'The quantity is less than 60' 
    WHEN UnitsInStock < 70 THEN 'The quantity is less than 70' 
    WHEN UnitsInStock < 80 THEN 'The quantity is less than 80' 
    WHEN UnitsInStock < 90 THEN 'The quantity is less than 90' 
    WHEN UnitsInStock < 100 THEN 'The quantity is less than 100' 
    WHEN UnitsInStock < 110 THEN 'The quantity is less than 110'   --Non compliant code
    WHEN UnitsInStock < 120 THEN 'The quantity is less than 120'   --Non compliant code
    ELSE 'The quantity is greater than or equal to 120'
END AS QuantityText
FROM Products