`WHILE`, `BREAK` and `CONTINUE`: repeat with visible boundary

SQL is primarily designed to operate on sets: to state which rows should be affected without visiting them one by one. Still, some processes need to repeat a block while a condition remains true.

WHILE creates the loop. BREAK terminates it and CONTINUE goes directly to the next condition evaluation.

Loop control

`WHILE`: repeat while a condition remains true

WHILE evaluates a condition before every iteration. If it is true, SQL Server executes the block and checks the condition again.

WHILE Condicion
BEGIN
    -- Instrucciones repetidas.
END;

`BREAK`: end the loop

BREAK exits the containing WHILE and continues with the statement after the loop.

IF CondicionDeSalida
    BREAK;

`CONTINUE`: move to the next iteration

CONTINUE skips the remaining statements in the current iteration and returns to the WHILE condition.

IF CondicionParaSiguienteVuelta
    CONTINUE;

The loop must modify some value related to its condition. Otherwise it can run indefinitely.

Example: cycle through months

DATEADD(MONTH, 1, @MesActual) adds one month to the current value. EOMONTH(@MesActual) returns the last day of that same month; both functions work with dates, not formatted texts.

DECLARE @MesActual DATE = '2026-01-01';
DECLARE @MesFinal DATE = '2026-04-01';

WHILE @MesActual < @MesFinal
BEGIN
    SELECT
        @MesActual AS InicioMes,
        EOMONTH(@MesActual) AS FinMes;

    SET @MesActual = DATEADD(MONTH, 1, @MesActual);
END;

The value advances one month with each turn. The condition ceases to be met before April.

Interrupt the cycle

DECLARE @Intento INT = 0;

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

    IF @Intento = 4
        BREAK;

    SELECT @Intento AS IntentoProcesado;
END;

BREAK ends the entire WHILE when the counter reaches 4.

Skip a lap

DECLARE @Numero INT = 0;

WHILE @Numero < 5
BEGIN
    SET @Numero += 1;

    IF @Numero = 3
        CONTINUE;

    SELECT @Numero AS NumeroProcesado;
END;

The number 3 does not reach SELECT, but the loop continues with 4. We update the counter before CONTINUE; placing it later could create a cycle without progress.

Batch processing

A practical use is to modify a controlled number of rows per iteration to limit transactions and locks. That pattern needs a stable key—a unique value that does not change between iterations—a condition that reduces pending work, and tracking of @@ROWCOUNT.

You should not improvise on production: a poorly defined batch cycle can repeat rows, skip them or compete indefinitely with other processes.

What should be checked

Before writing a WHILE, we ask whether one INSERT, UPDATE, DELETE, CTE, or window function can handle the set. A declarative solution states the desired result without prescribing a row-by-row traversal. It is usually clearer and gives more options to the optimizer, the component that compares execution strategies.

When the cycle is necessary, we make its output visible, establish limits and measure each turn. We also avoid keeping a transaction open throughout a long process without evaluating its impact.