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

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