WHILE | Teradata Vantage - WHILE - Advanced SQL Engine - Teradata Database

Purpose

Repeats the execution of a statement or statement list while a specified condition evaluates to true.

Invocation

Executable.

Stored procedures only.

Syntax

[ label_name : ] WHILE conditional_expression DO
  statement [...]
  END WHILE [ label_name ] ;

label_name

An optional label for the WHILE statement.

If an ending-label is specified, you must specify a beginning-label that is equivalent to the ending-label. The beginning-label must be terminated by a colon character (:).

The label name of the BEGIN … END compound statement cannot be reused in an iteration statement. One label name cannot be reused within one group of nested WHILE statements, but can be reused for different non-nesting iteration statements.

conditional_expression

A boolean condition used to evaluate whether a statement or statements embedded within the WHILE loop should be executed.

You cannot use IN and NOT IN operators if the conditional list contains any local variables, parameters, or cursor correlation names.

OUT parameters are not allowed in conditional_expression.

statement

A list of statements to be executed.

The list can contain any of the following:

• DML, DDL or DCL statements, including dynamic SQL.
• Control statements, including BEGIN … END.

For details, see statement in FOR.

ANSI Compliance

WHILE is ANSI/ISO SQL:2011-compliant.

Authorization

None.

Rules

• You can qualify WHILE with a label.
• You can specify a LEAVE or ITERATE statement within a WHILE statement.

Example: Using WHILE

WHILE hCounter > 0
DO
   INSERT INTO transaction (trans_num, account_num) 
    VALUES (hCounter, hAccountNum);
   SET hCounter = hCounter - 1;
END WHILE;

Example: Using WHILE to Set the High Number

WHILE hCounter > 0
DO
    SELECT highNum INTO maxNum
    FROM limits WHERE LIMIT_TYPE = ’HIGHNUM’;
    IF hCounter >= MaxNum THEN
        LEAVE LOOP1;
    END IF;
    INSERT INTO transaction (trans_num, account_num) 
     VALUES (hCounter, :hAccountNum);
    SET hCounter = hCounter - 1;
END WHILE;

Related Topics

For more information about the LEAVE or ITERATE statement, see ITERATE and LEAVE.