Export (0) Print
Expand All

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')
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft