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

T-SQL BREAK Statement

Master T-SQL BREAK — exit the innermost WHILE loop immediately. Search-and-stop pattern, exiting on threshold, behavior in nested loops, and comparison with CONTINUE and RETURN.

What is BREAK?

BREAK exits the innermost WHILE loop immediately, regardless of the loop's condition. Execution jumps to the first statement after the loop's END. Use it when you've discovered something during iteration that means "no point continuing".

Common Uses

Search-and-stop. Looking through rows or trying values; bail out as soon as you find what you wanted.
Error response. Something inside the loop failed and you need to terminate the iteration cleanly.
Bound on indefinite loops. A WHILE 1 = 1 infinite loop combined with a BREAK on a discovered exit condition is a clean way to handle "loop until done".

Syntax

WHILE condition BEGIN -- some statements IF stop_now BEGIN BREAK; END -- more statements (skipped after BREAK) END -- execution resumes here

⚠️ BREAK exits the innermost loop only. If your WHILE is nested inside another WHILE, BREAK ends only the inner one — the outer loop keeps going. To exit multiple loops at once, you need either a flag variable in the outer condition or a GOTO.

Examples

Example 1 — Exit on Threshold

T-SQL — Stop at 5

DECLARE @i INT = 1;

WHILE @i <= 10
BEGIN
    IF @i = 5
    BEGIN
        PRINT 'Exiting loop at value: ' + CAST(@i AS VARCHAR(10));
        BREAK;
    END

    PRINT 'i value: ' + CAST(@i AS VARCHAR(10));
    SET @i = @i + 1;
END

PRINT 'After the loop.';

OUTPUT

Messages

Commands completed successfully.

i value: 1

i value: 2

i value: 3

i value: 4

Exiting loop at value: 5

After the loop.

Notice the trace: 1 through 4 print, the BREAK message prints, then control jumps straight to "After the loop." — values 6–10 are never reached.

Example 2 — Search-and-Stop Pattern

Walk a list looking for the first match, then exit:

SQL — Setup

CREATE TABLE Numbers (n INT);
INSERT INTO Numbers VALUES (3), (7), (11), (19), (23), (29);

T-SQL — Find first prime > 15

DECLARE @i     INT = 1,
        @max   INT,
        @value INT,
        @found INT = NULL;

SELECT @max = COUNT(*) FROM Numbers;

WHILE @i <= @max
BEGIN
    SELECT @value = n
    FROM   (SELECT n, ROW_NUMBER() OVER (ORDER BY n) AS rn FROM Numbers) t
    WHERE  rn = @i;

    IF @value > 15
    BEGIN
        SET @found = @value;
        BREAK;                  -- stop at first hit
    END

    SET @i = @i + 1;
END

PRINT 'First number greater than 15: ' +
      ISNULL(CAST(@found AS VARCHAR(10)), '(none)');

OUTPUT

Messages

Commands completed successfully.

First number greater than 15: 19

BREAK vs CONTINUE vs RETURN

Statement	Effect	Goes to
`BREAK`	Exits the loop entirely	First statement after the loop's `END`
`CONTINUE`	Skips the rest of this iteration	Top of the loop — re-evaluates the WHILE condition
`RETURN`	Exits the entire batch / procedure / function	Caller — loop, surrounding code, everything ends

Conclusion

BREAK immediately exits the innermost WHILE loop, jumping to the first statement after its END.
Best for search-and-stop and "loop until something happens" patterns — pair with WHILE 1 = 1 for clean infinite loops with explicit exits.
Affects only one level of nesting — to exit multiple loops, use a flag variable or GOTO.
Compare with CONTINUE (skip current iteration) and RETURN (exit entire batch / procedure).