LOOP Statement

Quick Links:

Syntax

[ <statement-label>: ]
... [ WHILE <search-condition> ] LOOP
... <statement-list>
... END LOOP [ <statement-label> ]

Examples

(back to top)

Example 1 a WHILE loop in a procedure:

...
SET i = 1 ;
WHILE i <= 10 LOOP
  INSERT INTO Counters( number ) VALUES ( i ) ;
  SET i = i + 1 ;
END LOOP ;
...

Example 2 a labeled loop in a procedure:

SET i = 1;
lbl:
LOOP
  INSERT
  INTO Counters( number )
  VALUES ( i ) ;
  IF i >= 10 THEN
    LEAVE lbl ;
  END IF ;
  SET i = i + 1 ;
END LOOP lbl

Usage

(back to top)

The WHILE and LOOP statements are control statements that let you repeatedly execute a list of SQL statements while a <search-condition> evaluates to TRUE. The LEAVE statement can be used to resume execution at the first statement after the END LOOP.

If the ending <statement-label> is specified, it must match the beginning <statement-label>.

Standards

(back to top)

SQL—ISO/ANSI SQL compliant.
SAP Database products—Not supported in SAP ASE. The WHILE statement provides looping in Transact-SQL stored procedures.

Permissions

(back to top)

None