In SQL Server, the CONTINUE statement is used to skip the remaining code inside a WHILE loop for the current iteration. Unlike BREAK, which kills the loop entirely, CONTINUE forces the loop to jump back to the very beginning to check the condition again.

1. How it Works

When SQL Server hits a CONTINUE command:

  1. It ignores every line of code following it inside the BEGIN…END block.
  2. It goes straight back to the WHILE condition.
  3. If the condition is still true, it starts the next loop cycle.

2. Practical Example: Skipping Even Numbers

This script counts from 1 to 5 but uses CONTINUE to skip the number 2 and the number 4.

DECLARE @Counter INT = 0;

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

    -- Check if the number is even
    IF @Counter % 2 = 0
    BEGIN
        -- Skip the PRINT statement and go back to the top
        CONTINUE;
    END

    PRINT 'Processing Odd Number: ' + CAST(@Counter AS VARCHAR);
END

Output:

  • Processing Odd Number: 1
  • Processing Odd Number: 3
  • Processing Odd Number: 5

3. The “Infinite Loop” Trap

A common mistake when using CONTINUE is forgetting to increment your counter before the CONTINUE statement.

Bad Example (Will run forever):

DECLARE @Count INT = 1;
WHILE @Count <= 5
BEGIN
    IF @Count = 2
        CONTINUE; -- It jumps to the top, @Count is still 2, so it hits CONTINUE again...
    
    PRINT @Count;
    SET @Count = @Count + 1; -- This line is never reached if @Count is 2!
END

4. BREAK vs. CONTINUE

These two are often used together to manage complex logic:

Keyword Effect on the Loop Real-world Analogy
BREAK Exit the loop immediately. Quitting the job entirely.
CONTINUE Skip to the next iteration. Skipping a difficult task to move to the next one.

5. Use Case: Data Validation

You might use CONTINUE in a loop that processes records to skip “bad data” without stopping the entire batch.

WHILE EXISTS (SELECT 1 FROM @MyWorkTable)
BEGIN
    SELECT TOP 1 @ID = ID, @Email = Email FROM @MyWorkTable;

    IF @Email IS NULL
    BEGIN
        DELETE FROM @MyWorkTable WHERE ID = @ID;
        CONTINUE; -- Skip processing this row and move to the next
    END

    -- Perform expensive processing here
    EXEC SendEmail @Email;
    
    DELETE FROM @MyWorkTable WHERE ID = @ID;
END

Would you like to see how to combine CONTINUE with TRY...CATCH to skip records that cause errors during a loop?

Categorized in:

Stored Procedures,