WHILE loops are a fundamental concept in T-SQL programming, allowing us to execute a block of code repeatedly as long as a specified condition is true. Nested WHILE loops take this concept further by placing one WHILE loop inside another, enabling more complex iterations.

This guide will walk you through the basics of nested WHILE loops in SQL Server, complete with simple examples and a demonstration of the BREAK statement.

Understanding Nested WHILE Loops

A nested WHILE loop is simply a WHILE loop inside another WHILE loop. The inner loop completes all its iterations for each iteration of the outer loop. This structure is useful for when we need to perform repetitive tasks with multiple levels of iteration.

Basic Syntax

Here’s the basic syntax for a nested WHILE loop in T-SQL:

WHILE (outer_condition)
BEGIN
    -- Outer loop code
    WHILE (inner_condition)
    BEGIN
        -- Inner loop code
    END
    -- More outer loop code
END

So we can see that there are two WHILE loops; an outer loop and an inner loop.

Example

Here’s a basic example to demonstrate:

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));
        
        SET @inner_counter = @inner_counter + 1;
    END
    
    PRINT 'Outer loop continues after inner loop completes';
    SET @outer_counter = @outer_counter + 1;
END

PRINT 'All loops completed';

Output:

Outer loop iteration: 1
  Inner loop iteration: 1
  Inner loop iteration: 2
  Inner loop iteration: 3
  Inner loop iteration: 4
  Inner loop iteration: 5
Outer loop continues after inner loop completes
Outer loop iteration: 2
  Inner loop iteration: 1
  Inner loop iteration: 2
  Inner loop iteration: 3
  Inner loop iteration: 4
  Inner loop iteration: 5
Outer loop continues after inner loop completes
Outer loop iteration: 3
  Inner loop iteration: 1
  Inner loop iteration: 2
  Inner loop iteration: 3
  Inner loop iteration: 4
  Inner loop iteration: 5
Outer loop continues after inner loop completes
All loops completed

We can see by the output that for each iteration of the outer loop, the inner loop completed all of its iterations. So the inner loop ended up doing this three times (due to the fact that the outer loop had three iterations).

The BREAK Argument

T-SQL WHILE loops can have a BREAK argument, which causes an exit from the innermost WHILE loop. Any statements that appear after the END keyword, marking the end of the loop, are executed.

So in other words, if the 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.

Here’s a modified version of the previous example, with the inner loop containing a BREAK:

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

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

Another Example: Multiplication Table

Putting aside the BREAK argument, let’s do another basic WHILE loop.

This example generates a multiplication table for numbers 1 through 5:

DECLARE @i INT = 1;
DECLARE @j INT;

WHILE (@i <= 5)
BEGIN
    SET @j = 1;
    WHILE (@j <= 5)
    BEGIN
        PRINT CONCAT(@i, ' x ', @j, ' = ', @i * @j);
        SET @j = @j + 1;
    END
    PRINT ''; -- Print an empty line between rows
    SET @i = @i + 1;
END

Output:

1 x 1 = 1
1 x 2 = 2
1 x 3 = 3
1 x 4 = 4
1 x 5 = 5
 
2 x 1 = 2
2 x 2 = 4
2 x 3 = 6
2 x 4 = 8
2 x 5 = 10
 
3 x 1 = 3
3 x 2 = 6
3 x 3 = 9
3 x 4 = 12
3 x 5 = 15
 
4 x 1 = 4
4 x 2 = 8
4 x 3 = 12
4 x 4 = 16
4 x 5 = 20
 
5 x 1 = 5
5 x 2 = 10
5 x 3 = 15
5 x 4 = 20
5 x 5 = 25

While this is merely a simple multiplication example, we can start to see the potential of using nested WHILE loops in the right place in our T-SQL code.

Conclusion

Nested WHILE loops in T-SQL are a powerful tool for handling complex iterations. By understanding how to structure these loops and use statements like BREAK, we can create more efficient and flexible SQL scripts.

Remember to always be cautious when using loops in SQL, especially with large datasets, as they can be resource-intensive. In many cases, set-based operations are more efficient in SQL. However, for certain tasks, nested WHILE loops can be the right tool for the job.