Home

Description

The WHEN clauses should not have too many lines of code rule is a best practice for SQL Server code. This rule states that when writing a SQL Server query, each WHEN clause should contain no more than a few lines of code. This will help to improve readability and reduce complexity, while also making it easier to debug and maintain the query. Additionally, having too many lines of code in a WHEN clause can lead to performance issues.

Key Benefits

• Reduced Complexity: When clauses should not have too many lines of code as this reduces complexity and makes the code easier to read and debug.
• Faster Execution: When clauses with fewer lines of code are generally faster to execute than those with more lines.
• Improved Maintainability: When clauses with fewer lines of code are easier to maintain and update, as the code is simpler and more organized.

Non-compliant Code Example

DECLARE @Number int; 
Select 
case when @Number <= 100 then   --Non compliant code (When clause is having line of code more then default defined limit 5)
    case when @Number <= 90 then 
        case when @Number <= 80 then 
            case when @Number <= 70 then 
                case when @Number <= 60 then 
                    case when @Number <= 50 then 
                        case when @Number <= 40 then 
                            case when @Number <= 30 then 
                            20
                            End
                        End
                    End
                End
            End
        End
    End
End

Compliant Code Example

Declare @Number int  
Set @Number = 20
Select 
case when @Number <= 100 then  --Compliant code (When clause is having line of code less then default defined limit 5)
    case when @Number <= 90 then 
        20
    End
End