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:
  • If the GOTO statement is defined in a FOR statement, the label must be defined inside the same FOR statement, unless it is in a nested FOR statement or nested compound statement.
  • If the GOTO statement is defined in a compound statement, the label must be defined in side the same compound statement, unless it is in a nested FOR statement or nested compound statement.
  • If the GOTO statement is defined in a handler, the label must be defined in the same handler, following the other scope rules.
  • If the GOTO statement is defined outside of a handler, the label must not be defined within a handler.
  • If the label is not defined within a scope that the GOTO statement can reach, an error is returned (SQLSTATE 42736).