DB2 Version 9.7 for Linux, UNIX, and Windows

GOTO statement in SQL procedures

The GOTO statement is a straightforward and basic flow of control statement that causes an unconditional change in the flow of control. It is used to branch to a specific user-defined location using labels defined in the SQL procedure.

Use of the GOTO statement is generally considered to be poor programming practice and is not recommended. Extensive use of GOTO tends to lead to unreadable code especially when procedures grow long. Besides, GOTO is not necessary because there are better statements available to control the execution path. There are no specific situations that require the use of GOTO; instead it is more often used for convenience.

Here is an example of an SQL procedure that contains a GOTO statement:
  CREATE PROCEDURE adjust_salary ( IN p_empno CHAR(6),
  		                   IN p_rating INTEGER,
				   OUT p_adjusted_salary DECIMAL (8,2) )
  LANGUAGE SQL
  BEGIN
    DECLARE new_salary DECIMAL (9,2);
    DECLARE service DATE;  -- start date

    SELECT salary, hiredate INTO v_new_salary, v_service
      FROM employee
         WHERE empno = p_empno;

    IF service > (CURRENT DATE - 1 year) THEN
      GOTO exit;                   
    END IF;

    IF p_rating = 1 THEN
      SET new_salary = new_salary + (new_salary * .10);  
    END IF;

    UPDATE employee SET salary = new_salary WHERE empno = p_empno;

  exit: 
    SET p_adjusted_salary = v_new_salary;


  END

This example demonstrates what of the good uses of the GOTO statement: skipping almost to the end of a procedure or loop so as not to execute some logic, but to ensure that some other logic does still get executed.

You should be aware of a few additional scope considerations when using the GOTO statement: