WHILE loops are a powerful feature in most programming languages that allow developers to execute a block of code repeatedly as long as a specified condition is true. However, there are times when we may need to exit a loop prematurely based on certain criteria. SQL Server caters for this possibility with the BREAK keyword.

In this article, we’ll explore how to effectively use the BREAK keyword within WHILE loops in SQL Server.

Understanding WHILE Loops

Before diving into the BREAK keyword, let’s quickly review how WHILE loops work in SQL Server:

WHILE condition
BEGIN
    -- Code to be executed
END

The loop continues to execute as long as the specified condition evaluates to TRUE.

Introducing the BREAK Keyword

The BREAK keyword is an optional argument that allows us to exit a WHILE loop immediately, regardless of the loop’s condition. When encountered, BREAK causes the execution to jump to the first statement after the END of the WHILE loop.

When using nested loops, if an inner loop contains a BREAK, it exits to the next outermost loop. All statements after the end of the inner loop run first, and then the next outermost loop restarts.

When to Use BREAK

Common scenarios for using BREAK include:

• Exiting a loop early when a specific condition is met
• Implementing a “do-while” loop structure
• Preventing infinite loops

Example: Using BREAK in a WHILE Loop

Let’s look at a simple example:

DECLARE @counter INT = 1;

WHILE @counter <= 10
BEGIN
    IF @counter = 5
    BEGIN
        PRINT 'Breaking at 5';
        BREAK;
    END
    
    PRINT 'Counter: ' + CAST(@counter AS VARCHAR(2));
    SET @counter = @counter + 1;
END

PRINT 'Loop ended';

Output:

Counter: 1
Counter: 2
Counter: 3
Counter: 4
Breaking at 5
Loop ended

In this example, the loop is set to run 10 times, but we use BREAK to exit when the counter reaches 5.

Example: Nested Loop

As mentioned, when using nested loops, if an inner loop contains a BREAK, it exits to the next outermost loop. All statements after the end of the inner loop run first, and then the next outermost loop restarts.

Example:

DECLARE @outer_counter INT = 1;
DECLARE @inner_counter INT;

WHILE @outer_counter <= 3
BEGIN
    PRINT 'Outer loop iteration: ' + CAST(@outer_counter AS VARCHAR(2));
    
    SET @inner_counter = 1;
    
    WHILE @inner_counter <= 5
    BEGIN
        PRINT '  Inner loop iteration: ' + CAST(@inner_counter AS VARCHAR(2));
        
        IF @inner_counter = 2
        BEGIN
            PRINT '  Breaking inner loop';
            BREAK;
        END
        
        SET @inner_counter = @inner_counter + 1;
    END
    
    PRINT 'Outer loop continues after inner loop break';
    SET @outer_counter = @outer_counter + 1;
END

PRINT 'All loops completed';

Output:

Outer loop iteration: 1
  Inner loop iteration: 1
  Inner loop iteration: 2
  Breaking inner loop
Outer loop continues after inner loop break
Outer loop iteration: 2
  Inner loop iteration: 1
  Inner loop iteration: 2
  Breaking inner loop
Outer loop continues after inner loop break
Outer loop iteration: 3
  Inner loop iteration: 1
  Inner loop iteration: 2
  Breaking inner loop
Outer loop continues after inner loop break
All loops completed

In this case the inner loop exited whenever it reached two iterations (even though it would have otherwise iterated five times).

So the outer loop completed its three iterations as specified, but the inner loop only completed two iterations each time it ran.

BREAK vs. Other Control Flow Statements

It’s worth noting the differences between BREAK and other control flow statements:

• CONTINUE: Skips the rest of the current iteration and moves to the next one.
• RETURN: Exits the entire procedure, batch, or statement block. Statements that follow RETURN are not executed.
• BREAK: Exits only the current loop. As shown in the previous example, if this is a nested loop, then it exits to the next outermost loop and that loop will continue to iterate.

Best Practices and Considerations

When using BREAK in WHILE loops:

• Use clear and descriptive comments to explain the exit condition.
• Be cautious of nested loops, as BREAK only exits the innermost loop.
• Consider alternative loop structures if you find yourself using BREAK frequently.