Using WHILE...BREAK or CONTINUE
Collapse the table of content
Expand the table of content
This documentation is archived and is not being maintained.

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.

ms190385.note(en-US,SQL.90).gifNote:
If a SELECT statement is used as the condition for the WHILE statement, the SELECT statement must be in parentheses.

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

USE AdventureWorks;
GO
DECLARE abc CURSOR FOR
SELECT * FROM Purchasing.ShipMethod;
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 LastName FROM Person.Contact WHERE FirstName = N'Anne')
Show:
© 2016 Microsoft