while

Description

Sets a condition for the repeated execution of a statement or statement block. The statement(s) are executed repeatedly, as long as the specified condition is true.

Syntax

while logical_expression [plan "abstract plan"]
	statement

Parameters

logical_expression: is any expression that returns TRUE, FALSE, or NULL.

plan “abstract plan”: specifies the abstract plan to use to optimize the query. It can be a full or partial plan, specified in the abstract plan language. Plans can only be specified for optimizable SQL statements, that is, queries that access tables. See Chapter 30, “Creating and Using Abstract Plans,” in the Performance and Tuning Guide for more information.

statement: can be a single SQL statement, but is usually a block of SQL statements delimited by begin and end.

Examples

Example 1

If the average price is less than $30, double the prices of all books in the titles table. As long as it is still less than $30, the while loop keeps doubling the prices. In addition to determining the titles whose price exceeds $20, the select inside the while loop indicates how many loops were completed (each average result returned by Adaptive Server indicates one loop):

while (select avg(price) from titles) < $30
begin
    select title_id, price
        from titles
        where price > $20
    update titles
        set price = price * 2
end

Usage

The execution of statements in the while loop can be controlled from inside the loop with the break and continue commands.
The continue command causes the while loop to restart, skipping any statements after the continue. The break command causes an exit from the while loop. Any statements that appear after the keyword end, which marks the end of the loop, are executed. The break and continue commands are often activated by if tests.

For example:
```
while (select avg(price) from titles) < $30 
begin 
        update titles 
        set price = price * 2 
    if (select max(price) from titles) > $50 
        break 
    else 
        if (select avg(price) from titles) > $30
           continue 
        print "Average price still under $30"
end 

select title_id, price from titles
        where price > $30
```
This batch continues to double the prices of all books in the titles table as long as the average book price is less than $30. However, if any book price exceeds $50, the break command stops the while loop. The continue command prevents the print statement from executing if the average exceeds $30. Regardless of how the while loop terminates (either normally or because of the break command), the last query indicates which books are priced over $30.
If two or more while loops are nested, the break command exits to the next outermost loop. All the statements after the end of the inner loop run, then the next outermost loop restarts.

WARNING! If a create table or create view command occurs within a while loop, Adaptive Server creates the schema for the table or view before determining whether the condition is true. This may lead to errors if the table or view already exists.

Standards

SQL92 – Compliance level: Transact-SQL extension.

Permissions

while permission defaults to all users. No permission is required to use it.