SQL Reference

GOTO Statement

The GOTO statement is used to branch to a user-defined label within an SQL routine.

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).

Rules

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

Examples

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)
             ELSE IF 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