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