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.
Examples
A. Using WHILE in a cursor
The following example uses a WHILE statement to control how many fetches are done.
USE AdventureWorks2008R2;
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.Person WHERE FirstName = N'Anne')
B. Using BREAK and CONTINUE with nested IF...ELSE and WHILE
In the following example, if the average list price of a product is less than $300, the WHILE loop doubles the prices and then selects the maximum price. If the maximum price is less than or equal to $500, the WHILE loop restarts and doubles the prices again. This loop continues doubling the prices until the maximum price is greater than $500, and then exits the WHILE loop and prints a message.
USE AdventureWorks2008R2;
GO
WHILE (SELECT AVG(ListPrice) FROM Production.Product) < $300
BEGIN
UPDATE Production.Product
SET ListPrice = ListPrice * 2
SELECT MAX(ListPrice) FROM Production.Product
IF (SELECT MAX(ListPrice) FROM Production.Product) > $500
BREAK
ELSE
CONTINUE
END
PRINT 'Too much for the market to bear';