LOOP Statement

Repeats the execution of a statement list.

Syntax

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

Examples

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

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

SQL—ISO/ANSI SQL compliant.
Sybase—Not supported in Adaptive Server Enterprise. The WHILE statement provides looping in Transact-SQL stored procedures.

Permissions

None