Home

Description

The "IF ... ELSEIF constructs should end with ELSE clauses" rule states that when writing IF ... ELSEIF code in SQL Server, the last condition should always be an ELSE clause. This ensures that all possible conditions are accounted for, and helps to prevent unexpected results. The ELSE clause should be added even if no action is required, as this will make the code more readable and easier to maintain.

Key Benefits

• Ensures completeness: Adding an ELSE clause at the end of an IF-ELSEIF construct ensures that all possible outcomes are covered.
• Improves readability: ELSE clauses make the code more readable by making it obvious that all possible outcomes have been accounted for.
• Reduces errors: ELSE clauses reduce the possibility of errors caused by omitting an outcome.

Non-compliant Code Example

DECLARE @Number int;  
SET @Number = 50;  
IF @Number > 100 
    BEGIN
        Select * From Employee Where id < @Number;
        PRINT 'The number is large.'; 
    END
ELSE IF @Number < 10   --Non compliant code (IF Else block is not ending with Else clause)
        Select * From Employee Where id < @Number;
        PRINT 'The number is small.';
GO

Compliant Code Example

DECLARE @Number int;  
SET @Number = 50;  
IF @Number > 100 
    BEGIN
        Select * From Employee Where id < @Number;
        PRINT 'The number is large.';  
    END
ELSE IF @Number < 10  
    BEGIN
        Select * From Employee Where id < @Number;
        PRINT 'The number is small.';  
    END
ELSE   --Compliant code (IF Else block is ending with Else clause)
	PRINT 'The number is medium.'; 
GO