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 Chapter 9 “Search Conditions”
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;