WHILE statement [T-SQL]

Provides repeated execution of a statement or compound statement.

Syntax

WHILE search-condition statement

Remarks

The WHILE conditional affects the execution 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.

Privileges

None.

Side effects

None.

Standards

ANSI/ISO SQL Standard
The WHILE statement is part of optional ANSI/ISO SQL language feature P002, "Computational completeness". The Transact-SQL variant of the WHILE statement does not include END WHILE.

Example

The following code illustrates the use of WHILE:

WHILE ( SELECT AVG(UnitPrice) FROM GROUPO.Products ) < $30
BEGIN
   UPDATE GROUPO.Products
   SET UnitPrice = UnitPrice + 2
   IF ( SELECT MAX(UnitPrice) FROM GROUPO.Products ) > $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.