WHILE

The WHILE statement is used to allow looping within a procedure.

Scope

Procedures only

SQL Syntax

WHILE Condition DO [Statement; [...]] ENDWHILE;

Parameters

Condition: specifies anything that is allowed in a search condition except subqueries, column references, host variables, dynamic parameters, aggregate functions, string functions, date/time functions involving column references, long column functions, or TID functions. Local variables, built-in variables, and parameters may be included. See the "Search Conditions" chapter.
Statement: is any SQL statement allowed in a procedure-- including a compound statement.

Description

Statement is executed as long as the Condition evaluates to TRUE.
WHILE statements can be nested.
Local variables, built-in variables, and parameters can be used in a procedure as host variables are used in an application program.

Authorization

Anyone can use the WHILE statement.

Example

Create and execute a procedure to display all the quantities in the LargeOrders table for a given part:

   CREATE PROCEDURE ShowOrders AS

      BEGIN

         DECLARE Quantity INTEGER;

         DECLARE PartName CHAR(16);



         DECLARE QtyCursor CURSOR FOR

            SELECT PartName, Quantity

            FROM LargeOrders;



         OPEN QtyCursor;

         WHILE ::sqlcode <> 100 DO

            FETCH QtyCursor INTO :PartName, :Quantity

            PRINT :PartName;

            PRINT :Quantity;

         ENDWHILE;

         CLOSE QtyCursor;

      END;

   EXECUTE PROCEDURE ShowOrders;