Using WHILE...BREAK or CONTINUE

The WHILE statement repeats a statement or block of statements as long as a specified condition remains true.

Two Transact-SQL statements are commonly used with WHILE: BREAK or CONTINUE. The BREAK statement exits the innermost WHILE loop and the CONTINUE statement restarts a WHILE loop. A program might execute a BREAK statement if, for example, there are no other rows to process. A CONTINUE statement could be executed if, for example, the execution of the code should continue.

Note If a SELECT statement is used as the condition for the WHILE statement, the SELECT statement must be in parentheses.

This example uses a WHILE statement to control how many fetches are done:

USE Northwind

GO

DECLARE abc CURSOR FOR

SELECT * FROM Shippers

OPEN abc

FETCH NEXT FROM abc

WHILE (@@FETCH_STATUS = 0)

    FETCH NEXT FROM abc

CLOSE abc

DEALLOCATE abc

GO

Other valid WHILE condition tests could be the following:

WHILE (@ACounterVariable < 100)

Or

WHILE EXISTS(SELECT au_lname FROM authors WHERE au_fname = 'Anne')

See Also