SQL Server WHILE Loop (with Examples)

Posted on by Ian

The WHILE loop in SQL Server is a control-flow statement that allows us to repeatedly execute a block of code as long as a specified condition is true. It’s useful for iterative tasks and processing data in batches.

Syntax

The basic syntax goes like this:

WHILE condition
BEGIN
    -- Code to be executed
END

Key points:

  1. The condition is evaluated before each iteration.
  2. If the condition is true, the code block executes.
  3. If the condition is false, the loop terminates.

The SQL Server documentation provides the following, more specific, syntax (which is basically an elaboration on the above simple syntax):

WHILE boolean_expression
    { sql_statement | statement_block | BREAK | CONTINUE }

The following examples demonstrate the how the syntax translates to actual T-SQL code.

Example

Here’s an example of a simple WHILE loop:

DECLARE @counter INT = 1;

WHILE @counter <= 5
BEGIN
    PRINT 'Iteration: ' + CAST(@counter AS VARCHAR(2));
    SET @counter = @counter + 1;
END

Result:

Iteration: 1
Iteration: 2
Iteration: 3
Iteration: 4
Iteration: 5

WHILE Loop with BREAK

The BREAK argument causes an exit from the innermost WHILE loop. Any statements that appear after the END keyword, marking the end of the loop, are executed.

Example:

DECLARE @num INT = 1;

WHILE 1 = 1 -- Infinite loop
BEGIN
    IF @num > 5
        BREAK;
    PRINT @num;
    SET @num = @num + 1;
END

Result:

1
2
3
4
5

While this example uses an infinite loop, we usually want to avoid infinite loops. But the example demonstrates that we can break out of the loop based on a certain condition being met.

WHILE Loop with CONTINUE

The CONTINUE argument restarts a WHILE loop. Any statements after the CONTINUE keyword are ignored. CONTINUE is often opened by an IF test.

Example:

DECLARE @i INT = 0;

WHILE @i < 10
BEGIN
    SET @i = @i + 1;
    IF @i % 2 = 0
        CONTINUE;
    PRINT 'Odd number: ' + CAST(@i AS VARCHAR(2));
END

Result:

Odd number: 1
Odd number: 3
Odd number: 5
Odd number: 7
Odd number: 9

Nested WHILE Loop

It’s possible to nest WHILE loops, so that we have a loop within another loop.

Here’s a simple 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));
        
        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

If the inner loop contains a BREAK, it exits to the next outermost loop. All the statements after the end of the inner loop run first, and then the next outermost loop restarts:

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

WHILE Loop for Data Processing

Suppose we create the following table:

-- Create the Orders table
CREATE TABLE Orders (
    OrderID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerName VARCHAR(100),
    OrderDate DATE,
    TotalAmount DECIMAL(10,2),
    Processed BIT DEFAULT 0
);

-- Insert sample data
INSERT INTO Orders (CustomerName, OrderDate, TotalAmount)
VALUES 
    ('John Doe', '2024-07-20', 150.00),
    ('Jane Smith', '2024-07-21', 200.50),
    ('Bob Johnson', '2024-07-22', 75.25),
    ('Alice Brown', '2024-07-23', 300.00),
    ('Charlie Davis', '2024-07-24', 125.75);

-- View the initial data
SELECT * FROM Orders;

Result:

OrderID  CustomerName   OrderDate                 TotalAmount  Processed
-------  -------------  ------------------------  -----------  ---------
1        John Doe       2024-07-20T00:00:00.000Z  150          false    
2        Jane Smith     2024-07-21T00:00:00.000Z  200.5        false    
3        Bob Johnson    2024-07-22T00:00:00.000Z  75.25        false    
4        Alice Brown    2024-07-23T00:00:00.000Z  300          false    
5        Charlie Davis  2024-07-24T00:00:00.000Z  125.75       false

We can use the following code to “process” the orders, one by one with a WHILE loop:

-- Process orders using a WHILE loop
DECLARE @id INT;

WHILE EXISTS (SELECT 1 FROM Orders WHERE Processed = 0)
BEGIN
    SELECT TOP 1 @id = OrderID FROM Orders WHERE Processed = 0;
    
    -- Process the order (in this example, we're just marking it as processed)
    UPDATE Orders 
    SET Processed = 1 
    WHERE OrderID = @id;
    
    -- Simulate some processing time
    WAITFOR DELAY '00:00:01';
    
    PRINT 'Processed OrderID: ' + CAST(@id AS VARCHAR(10));
END

Output:

(1 row affected)
Processed OrderID: 1
(1 row affected)
Processed OrderID: 2
(1 row affected)
Processed OrderID: 3
(1 row affected)
Processed OrderID: 4
(1 row affected)
Processed OrderID: 5

In the real world we would do some actual processing, but seeing as this is just a quick example, all we do is set the Processed column to 1 (for true) and simulate a bit of processing time.

Once that code has run, we can check the data in the table again:

SELECT * FROM Orders;

Result:

OrderID  CustomerName   OrderDate                 TotalAmount  Processed
-------  -------------  ------------------------  -----------  ---------
1        John Doe       2024-07-20T00:00:00.000Z  150          true     
2        Jane Smith     2024-07-21T00:00:00.000Z  200.5        true     
3        Bob Johnson    2024-07-22T00:00:00.000Z  75.25        true     
4        Alice Brown    2024-07-23T00:00:00.000Z  300          true     
5        Charlie Davis  2024-07-24T00:00:00.000Z  125.75       true

We can see that all orders have now been processed.

These examples demonstrate that the WHILE loop can be a great tool in SQL Server for handling repetitive tasks. However, it’s a good idea to use it judiciously, as set-based operations are often more efficient for large-scale data processing.