Home

Description

The "Related IF/ELSE IF statements and WHEN clauses in a CASE should not have the same condition" SQL Server code rule states that when using IF/ELSE IF statements and WHEN clauses in a CASE statement, the conditions should be different. This ensures that the correct logic is being used and that the code is not redundant. If the same condition is used for multiple IF/ELSE IF statements or WHEN clauses, then the code will not be as efficient as it could be and could lead to unexpected results. It is important to ensure that the conditions are different for each IF/ELSE IF statement and WHEN clause in order to ensure that the code is efficient and that the results are as expected.

Key Benefits

• Eliminates Redundancy: By avoiding using the same condition in IF/ELSE IF statements and WHEN clauses in a CASE, it eliminates the risk of redundant code.
• Simplifies Maintenance: By avoiding using the same condition in IF/ELSE IF statements and WHEN clauses in a CASE, it simplifies the maintenance of the code.
• Improves Readability: By avoiding using the same condition in IF/ELSE IF statements and WHEN clauses in a CASE, it improves the readability of the code.

Non-compliant Code Example

SELECT ProductNumber, Category =  
CASE ProductLine  
    WHEN 'R' THEN 'Road'  
    WHEN 'M' THEN 'Mountain'   --Non compliant code (When clauses is having similar condition)
    WHEN 'T' THEN 'Touring'  
    WHEN 'M' THEN 'Sale items'   --Non compliant code (When clauses is having similar condition)
    ELSE 'Not for sale'  
END,  
Name  
FROM Production.Product  
ORDER BY ProductNumber;

Compliant Code Example

SELECT ProductNumber, Category =    --Compliant code (When clauses is having different condition)
CASE ProductLine
    WHEN 'R' THEN 'Road'
    WHEN 'M' THEN 'Mountain'
    WHEN 'T' THEN 'Touring'
    WHEN 'S' THEN 'Sale items'
    ELSE 'Not for sale'
END,
Name
FROM Production.Product
ORDER BY ProductNumber;