GOTO statement

Invocation

This statement can only be embedded in an SQL procedure. It is not an executable statement and cannot be dynamically prepared.

Authorization

None required.

Syntax

>>-GOTO--label-------------------------------------------------><

Description

label

Specifies a labelled statement where processing is to continue. The labelled statement and the GOTO statement must be in the same scope:

• If the GOTO statement is defined in a FOR statement, label must be defined inside the same FOR statement, excluding a nested FOR statement or nested compound statement
• If the GOTO statement is defined in a compound statement, label must be defined inside the same compound statement, excluding a nested FOR statement or nested compound statement
• If the GOTO statement is defined in a handler, label must be defined in the same handler, following the other scope rules
• If the GOTO statement is defined outside of a handler, label must not be defined within a handler.

If label is not defined within a scope that the GOTO statement can reach, an error is returned (SQLSTATE 42736).

Notes

• It is recommended that the GOTO statement be used sparingly. This statement interferes with normal processing sequences, thus making a routine more difficult to read and maintain. Before using a GOTO statement, determine whether another statement, such as IF or LEAVE, can be used in place, to eliminate the need for a GOTO statement.

Example

In the following compound statement, the parameters rating and v_empno are passed into the procedure, which then returns the output parameter return_parm as a date duration. If the employee's time in service with the company is less than 6 months, the GOTO statement transfers control to the end of the procedure, and new_salary is left unchanged.

   CREATE PROCEDURE adjust_salary
     (IN v_empno CHAR(6),
     IN rating INTEGER,
     OUT return_parm DECIMAL (8,2))
     MODIFIES SQL DATA
     LANGUAGE SQL
     BEGIN
       DECLARE new_salary DECIMAL (9,2);
       DECLARE service DECIMAL (8,2);
         SELECT SALARY, CURRENT_DATE - HIREDATE
           INTO new_salary, service
           FROM EMPLOYEE
           WHERE EMPNO = v_empno;
         IF service < 600
           THEN GOTO EXIT;
         END IF;
         IF rating = 1
           THEN SET new_salary = new_salary + (new_salary * .10);
         ELSEIF rating = 2
           THEN SET new_salary = new_salary + (new_salary * .05);
         END IF;
         UPDATE EMPLOYEE
           SET SALARY = new_salary
           WHERE EMPNO = v_empno;
         EXIT: SET return_parm = service;
   END