LuisLlamas Logo
bucle-while-sql

The WHILE Loop in SQL

  • 4 min
  • Advanced

The WHILE loop in T-SQL is a control structure that allows repeating a block of code as long as a specific condition is met.

In most programming languages (Python, C#, Java), loops are everyday tools. We use them to iterate through lists, arrays, or process files.

In SQL Server, the story is a bit different. SQL is a set-oriented language. It’s designed to process 1 million rows at once with a single UPDATE or SELECT statement.

However, sometimes there’s no choice but to get your hands dirty and process something step by step. For these cases, T-SQL offers us only one type of loop, the WHILE loop.

Basic Syntax

The structure is very simple: While the condition is true, the code inside the BEGIN...END block will repeat.

WHILE Condition
BEGIN
    -- Code to repeat
    -- ¡IMPORTANTE! Something must change here so the condition eventually becomes false
END
Copied!
  • Condition: A boolean expression evaluated before each iteration. If the condition is true (TRUE), the code block inside BEGIN...END executes. If false (FALSE), the loop ends.
  • BEGIN…END: Defines the block of code that will execute in each loop iteration.

Basic example

Imagine we want to print numbers from 1 to 5 using a WHILE loop. The code would be as follows:

DECLARE @counter INT = 1;

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

In this example:

  • We declare a variable @counter and initialize its value to 1.
  • The condition @counter <= 5 ensures the loop executes while the value of @counter is less than or equal to 5.
  • Inside the loop, we print the current value of @counter and then increment its value by 1 with SET @counter = @counter + 1.
  • The loop ends when @counter reaches the value 6.

Beware of Infinite Loops! If you forget the line SET @Counter = @Counter + 1, the condition @Counter <= 5 will always be true (1 is always less than 5).

Your script will run forever, consuming CPU until you cancel it manually (or the server crashes 💥). Always check your exit logic.

Controlling the flow: BREAK and CONTINUE

Sometimes we need to alter the loop’s normal behavior. For that we have two commands:

Stops the current iteration and immediately returns to the beginning of WHILE to re-evaluate the condition, skipping any remaining code in that iteration.

Imagine we want to process only odd numbers.

DECLARE @k INT = 0;

WHILE @k < 10
BEGIN
    SET @k = @k + 1;

    -- If it's even (remainder when divided by 2 is 0), skip it
    IF @k % 2 = 0 
        CONTINUE; -- Go back to the top, don't execute the PRINT
        
    PRINT 'Odd number: ' + CAST(@k AS VARCHAR);
END
Copied!

Stops the loop immediately and jumps to the first line after END.

Imagine we are searching for something and, once found, we don’t want to keep iterating.

DECLARE @i INT = 1;

WHILE @i <= 100
BEGIN
    PRINT @i;
    
    IF @i = 5
    BEGIN
        PRINT 'Found 5. Let's go!';
        BREAK; -- Breaks the loop right here
    END

    SET @i = @i + 1;
END
Copied!

The Big Warning: RBAR (Row By Agonizing Row)

In the SQL Server world, there is a derogatory term for excessive loop usage: RBAR (Row By Agonizing Row).

Imagine you want to increase all employees’ salaries by 10% (simple example, but serves to demonstrate a point).

Correct option, thinking in SQL mode, using set-based instructions. Fast, a single hit to the database.

UPDATE Empleados SET Salario = Salario * 1.10;
-- Time: 0.05 seconds for 10,000 rows.
Copied!

Incorrect option, trying to do it as in functional programming. Make a WHILE loop that goes through the table, reads the ID, does an UPDATE for that row, and moves to the next.

-- Pseudocode
WHILE (rows remain)
BEGIN
   UPDATE Empleados SET Salario ... WHERE ID = @CurrentID;
END
-- Time: 15 seconds for 10,000 rows.
Copied!

So When to Use WHILE?

Loops in SQL are slow because they prevent the engine from optimizing the operation as a block.

  1. Administrative tasks: Backing up several databases one by one.
  2. Complex procedural logic: When what you have to do with each row is so complex (calling APIs, extreme conditional logic) that it doesn’t fit in a single query.
  3. Data generation: Creating test data (like inserting 1,000 dummy rows).

On this page