Reference Manual
   CHAPTER 9. SQL Statements     

LOOP statement

Function 

Repeat the execution of a statement list.

Syntax 

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

Permissions 

None.

Side effects 

None.

See also 

FOR statement

LEAVE statement

Description 

The WHILE and LOOP statements are control statements that allow you to 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 and compatibility 

• SQL/92     Persistent Stored Module feature.

• Sybase     Not supported in Adaptive Server Enterprise. The WHILE statement provides looping in Transact-SQL stored procedures.

Examples 

• 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 ;
  ...

• 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