while

Description

Sets a condition for the repeated execution of a statement or statement block. The statements 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

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

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

See also

Commands begin...end, break, continue, goto label