SQL Statements › CREATE PROCEDURE › CREATE PROCEDURE Syntax and Description › WHILE Statement

WHILE Statement

The WHILE statement defines conditional looping logic in a compound statement. It provides a means to loop while some predicate remains true. We recommend that you use the optional DATACOM LOOPLIMIT clause with this statement.

Following is the syntax for the WHILE statement:

►►─┬──────────────────────┬─ WHILE ─ proc-search-condition ─ DO ──────────────►
   └─ start-label: ───────┘

 ►─ proc-SQL-stmt-list ─ END WHILE ─┬─────────────┬───────────────────────────►
                                    └─ end-label ─┘

 ►─┬───────────────────────────────────────┬──────────────────────────────────►◄
   └─ DATACOM LOOPLIMIT ─ integer-literal ─┘

start-label: / end-label

(Optional) A start-label: is an SQL identifier (followed by a colon) that can be used in various flow-control statements to mark the destination of a branch. When you specify an end-label you must also specify a start-label, and the labels must match.

proc-search-condition

The proc-search-condition specifies a condition that is true, false, or unknown about a row. The proc-search-condition is similar to the search-condition described in with the following modifications that allow its use in SQL Procedures:

• SQL parameter and SQL variable references are allowed instead of column and host-variable references.
• Aggregate functions and any predicate involving a sub-query are not allowed.

proc-SQL-stmt-list

A proc-SQL-stmt-list is a list of proc-SQL-stmt statements, each terminated by a semicolon. For information about a proc-SQL-stmt, see CREATE PROCEDURE Syntax and Description.

DATACOM LOOPLIMIT

We recommend that you use DATACOM LOOPLIMIT. DATACOM LOOPLIMIT is a CA Datacom extension that we provide to allow your proactive avoidance of endless loops that might be caused by faulty logic, unexpected or missing column values, or other unforeseen conditions. The Multi-User startup option SQL_DATACOM_LOOPLIMIT can be used to force a default limit on each looping statement. An error is produced if the limit is exceeded.

integer-literal

The integer-literal specifies the maximum number of times the loop is allowed to execute.