WHILE Statement [T-SQL]

Provides repeated execution of a statement or compound statement.

Quick Links:

Syntax

WHILE expression
   ... statement

Examples

(back to top)

Example 1 – the BREAK statement breaks the WHILE loop, if the most expensive product has a price less than $50. Otherwise, the loop continues until the average price is greater than $30.
```
WHILE (SELECT AVG(unit_price) FROM Products) < 30 
BEGIN
	DELETE FROM Products
	WHERE UnitPrice = MAX(UnitPrice)
	IF ( SELECT MAX(UnitPrice) FROM Products ) < 50 
		BREAK
END
```

Usage

(back to top)

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

(back to top)

SQL—Transact-SQL extension to ISO/ANSI SQL grammar.
SAP Sybase Database product—Supported by Adaptive Server.

Permissions

(back to top)

None