Oracle PL/SQL WHILE T-SQL · Control-of-Flow · WHILE

T-SQL WHILE Loop

Master T-SQL WHILE loops — counting loops, calculating factorials, walking query results cursor-style. Includes infinite-loop pitfalls, the cardinal rule of always updating the loop variable, and WHILE vs cursor comparison.

What is WHILE?

The WHILE statement runs a block of code repeatedly as long as a Boolean condition stays TRUE. The condition is checked at the top of every iteration — so if it's already FALSE before the loop starts, the body never runs at all.

WHILE is T-SQL's only loop construct. Unlike languages with FOR, FOREACH, or DO...WHILE, you build any loop pattern you need with WHILE plus an explicit counter or exit condition.

Syntax

WHILE condition BEGIN -- statements to repeat END

condition — any Boolean expression. Re-evaluated before each iteration.
BEGIN ... END — needed when the body has more than one statement (which is virtually always).
Inside the body you can use BREAK to exit early or CONTINUE to skip to the next iteration.

⚠️ Infinite-loop danger. Every WHILE loop needs something that eventually makes the condition FALSE. The most common bug in T-SQL loops is forgetting to update the counter — the loop runs forever, holding locks and burning CPU until you cancel the query. Always check that the variable in the WHILE condition is modified inside the loop body.

Examples

Example 1 — Counting Loop

T-SQL — Print 1 to 5

DECLARE @i INT = 1;

WHILE @i <= 5
BEGIN
    PRINT @i;
    SET @i = @i + 1;        -- the loop driver — never forget this!
END

OUTPUT

Messages

Commands completed successfully.

Example 2 — Calculating Factorial

A classic example: count down while accumulating a product.

T-SQL — Factorial of 5

DECLARE @number    INT = 5,
        @factorial INT = 1;

WHILE @number > 0
BEGIN
    SET @factorial = @factorial * @number;
    SET @number    = @number - 1;
END

PRINT 'Factorial is: ' + CAST(@factorial AS VARCHAR(10));

OUTPUT

Messages

Commands completed successfully.

Factorial is: 120

📌 Why CAST? PRINT can only output strings. To concatenate the integer @factorial with the literal text, we convert it to VARCHAR first. Without the CAST, you'd get "Conversion failed".

Example 3 — Loop Until Done (Cursor-Style)

WHILE is often used to walk through query results one row at a time. The pattern: a counter or "next row exists" check drives the loop:

SQL — Setup

CREATE TABLE Tasks (
    TaskID  INT IDENTITY(1,1) PRIMARY KEY,
    Title   NVARCHAR(50)
);

INSERT INTO Tasks (Title) VALUES
    ('Backup database'),
    ('Update statistics'),
    ('Check disk space');

T-SQL — Walk a table with WHILE

DECLARE @id    INT = 1,
        @max   INT,
        @title NVARCHAR(50);

SELECT @max = MAX(TaskID) FROM Tasks;

WHILE @id <= @max
BEGIN
    SELECT @title = Title FROM Tasks WHERE TaskID = @id;
    IF @title IS NOT NULL
        PRINT 'Task ' + CAST(@id AS VARCHAR(10)) + ': ' + @title;
    SET @id = @id + 1;
END

OUTPUT

Messages

Commands completed successfully.

Task 1: Backup database

Task 2: Update statistics

Task 3: Check disk space

💡 Set-based first! Looping row-by-row is almost always slower than a single SQL statement that processes the whole set. Use WHILE for genuinely iterative work (computing factorials, retrying with backoff, time-based polling) — not as a substitute for proper SQL. If you find yourself reaching for WHILE to update many rows, ask "could a single UPDATE/MERGE do this?" first.

Infinite-Loop Pitfalls

The two ways every T-SQL developer eventually creates an infinite loop:

Pattern	The Bug	Fix
Forgot the increment	`WHILE @i <= 10 BEGIN PRINT @i; END` — `@i` never changes	Add `SET @i = @i + 1;` inside the loop
Increment after CONTINUE	Skip-condition fires; the line that increments comes after the `CONTINUE` and never runs	Increment before `CONTINUE`

If you suspect a loop is stuck, you can always cancel it in SSMS (the red square Stop button) — the connection rolls back any open transaction.

WHILE vs Cursors

Aspect	WHILE with counter	Cursor (DECLARE CURSOR)
Code volume	Less — just a variable and a condition	More — DECLARE / OPEN / FETCH / CLOSE / DEALLOCATE
Memory	Negligible	Maintains a server-side result set
Position tracking	You manage it (counter, MIN/MAX, etc.)	SQL Server manages it
Fits when	You can compute the next key from the current one	You need to walk an arbitrary, ordered query result

For most simple cases, WHILE is enough. Cursors are covered in their own section.

Conclusion

WHILE condition BEGIN ... END repeats a block while the Boolean stays TRUE; the condition is re-checked at the top of each iteration.
Always update the variable that drives the condition — forgetting to is the #1 cause of T-SQL infinite loops.
Use BREAK to exit early, CONTINUE to skip to the next iteration (and remember to advance the counter before CONTINUE).
Prefer set-based SQL over WHILE for data manipulation; reserve WHILE for genuinely iterative tasks.
For walking query results, WHILE with a counter or MIN/MAX is the lightweight option; cursors offer more structure when needed.