Home

Description

The "Control structures should use BEGIN...END blocks" rule for SQL Server requires that all control structures (e.g. IF, WHILE, etc.) should be enclosed in BEGIN and END statements. This helps to ensure that all statements within the control structure are executed as a single unit, which is important for ensuring the integrity of the data. It also makes the code easier to read and debug, as the start and end of each control structure is clearly marked. This rule is enforced by the SQL Server query optimizer, and any violation of this rule will result in an error.

Key Benefits

• Explicitness: BEGIN...END blocks make the code more explicit, thus reducing the chances of an unexpected behavior.
• Readability: BEGIN...END blocks make the code more readable, allowing developers to easily understand the code without having to guess the logic behind it.
• Maintainability: BEGIN...END blocks make the code more maintainable, as it is easier to modify and debug.
• Performance: BEGIN...END blocks can improve the performance of the code, as they can reduce the number of lines of code that need to be executed.

Non-compliant Code Example

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

Compliant Code Example

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