Labeled Statement

Scope

SQL Syntax

  Label:Statement

Parameters

Label

is an integer or a name which conforms to the SQL syntax rules for a basic name.

Statement

is the statement within a procedure to be labeled.

Description

• A label may appear only at the start of a ProcedureStatement that is not part of a compound statement. It cannot appear with a local variable declaration or a WHENEVER directive.

• Labels within a procedure should be unique.

• A label can only be referred to from a GOTO statement and WHENEVER...GOTO directive.

Authorization

Example

   CREATE PROCEDURE Process19 (param1 integer, param2 float) AS
   BEGIN
   DECLARE value1 integer;
   WHENEVER sqlerror GOTO errorexit;
   DECLARE cursor1 CURSOR FOR
     SELECT column1
       FROM table1
      WHERE column1 > :param1;
   OPEN cursor1;
   WHILE ::sqlcode < > 100 do
     FETCH cursor1 into :value1;
     IF ::sqlcode = 100 THEN
       GOTO loopexit;
     ENDIF;
     INSERT INTO table2
          VALUES (:value1, :param2);
     UPDATE table3 SET
        column1 = CURRENT_DATE WHERE column2 = :value1;
     IF ::sqlerrd2 < 1 THEN
       INSERT INTO table3
            VALUES (CURRENT_DATE, :value1);
     ENDIF;
   ENDWHILE;
   loopexit:
   CLOSE cursor1;
   RETURN 0;
   errorexit:
   PRINT 'Procedure terminated due to error:';
   PRINT ::sqlcode;
   END;
   EXECUTE PROCEDURE Process19;