Reference Manual
   CHAPTER 9. SQL Statements     

WHILE statement [T-SQL]

Function 

To provide repeated execution of a statement or compound statement.

Syntax 

WHILE expression
... statement

Authorization 

None.

Side effects 

None.

See also 

LOOP statement

Description 

The WHILE conditional affects the performance of only a single SQL statement, unless statements are grouped into a compound statement between the keywords BEGIN and END.

The BREAK statement and CONTINUE statement can be used to control execution of the statements in the compound statement. The BREAK statement terminates the loop, and execution resumes after the END keyword marking the end of the loop. The CONTINUE statement causes the WHILE loop to restart, skipping any statements after the CONTINUE.

Standards and compatibility 

• SQL/92     Transact-SQL extension.

• Sybase     Supported by Adaptive Server Enterprise.

Example 

• The following illustrates the use of WHILE:

  WHILE (SELECT AVG(unit_price) FROM product) < $30 
  BEGIN
     UPDATE product
     SET unit_price = unit_price + 2 
     IF ( SELECT MAX(unit_price) FROM product ) > $50 
        BREAK
  END

  The BREAK statement breaks the WHILE loop if the most expensive product has a price above $50. Otherwise, the loop continues until the average price is greater than or equal to $30.