MySQL - EXIT Stored Procedure

Question

CREATE OR REPLACE PROCEDURE <PROC_NAME>
    BEGIN
        CALL PROC1;
        CALL PROC2;
        CALL PROC3;
    END;

In all the above 3 calls, I have added DECLARE EXIT HANDLER FOR SQLEXCEPTION CALL PROC_ERROR('Proc_Name');

Now let's say there is a sqlexception in PROC2 and the control goes to PROC_ERROR where I execute ROLLBACK statement. After the call PROC_ERROR execution, the control still continues and it executes PROC3 procedure which should not happen. Ideally I want to end the execution as soon as the ROLLBACK is completed.

Answer 1

Inside your exception handler, after the ROLLBACK statement, you need to raise another exception. You can achieve this by using a RESIGNAL statement. See this for detail: http://guyharrison.squarespace.com/blog/2009/7/13/signal-and-resignal-in-mysql-54-and-60.html Because the RESIGNALed exception will not be handled, execution will end as soon as the ROLLBACK is completed.