In SQL Server, the BREAK statement is used to exit a WHILE loop immediately. When the system encounters BREAK, it stops the loop’s execution and moves to the first statement following the END keyword of that loop.

1. How it Works

Think of BREAK as an “emergency exit.” While the WHILE condition usually determines when a loop stops, BREAK allows you to stop the loop based on a separate condition inside the block.

Basic Syntax:

WHILE condition
BEGIN
    IF internal_condition
        BREAK; -- Exit the loop now
    
    -- Other code...
END

2. Practical Example

This loop is designed to run 10 times, but we use an IF statement with BREAK to stop it early if the counter hits 5.

DECLARE @Counter INT = 1;

WHILE @Counter <= 10
BEGIN
    IF @Counter = 5
    BEGIN
        PRINT 'Breaking the loop at 5';
        BREAK; 
    END

    PRINT 'Counter is: ' + CAST(@Counter AS VARCHAR);
    SET @Counter = @Counter + 1;
END

PRINT 'Loop exited.';

Output:

  • Counter is: 1
  • Counter is: 2
  • Counter is: 3
  • Counter is: 4
  • Breaking the loop at 5
  • Loop exited.

3. Using BREAK in Nested Loops

If you have a loop inside another loop (nested loops), the BREAK command only exits the innermost loop. The outer loop will continue to run.

WHILE condition1 -- Outer Loop
BEGIN
    WHILE condition2 -- Inner Loop
    BEGIN
        IF internal_condition
            BREAK; -- This ONLY exits the Inner Loop
    END
END

4. BREAK vs. CONTINUE

It is important not to confuse these two control-of-flow keywords:

Keyword Action Result
BREAK Exits the entire loop. Code execution jumps to the end of the loop.
CONTINUE Skips the current iteration. Code jumps back to the WHILE condition to try again.

5. Common Use Case: Infinite Loops

Sometimes developers use WHILE 1=1 (a condition that is always true) and rely entirely on BREAK to exit based on complex logic or data changes.

WHILE 1=1 -- Infinite loop
BEGIN
    -- Do some work
    IF (SELECT COUNT(*) FROM Tasks WHERE Status = 'Pending') = 0
        BREAK; -- Exit when no tasks are left
        
    WAITFOR DELAY '00:00:05'; -- Wait 5 seconds before checking again
END

Would you like to see how to use BREAK inside a loop that processes rows from a Cursor or a Table Variable?

Categorized in:

Stored Procedures,