MySQL Forums :: Stored Procedures :: Transaction/Rollback in a Stored Procedure which contains nested stored procs
Transaction/Rollback in a Stored Procedure which contains nested stored procs
Date: April 28, 2008 01:17PM
The following is an example of nested stored procedures, similar idea to what I'm trying to do. I am having issues with the rollback when an 'insert into test_table' throws an error. Is the following transaction even possible?
# main stored proc with transaction that calls another stored proc
CREATE PROCEDURE sp_testA()
DECLARE EXIT HANDLER FOR SQLEXCEPTION rollback;
declare exit handler for not found rollback;
declare exit handler for sqlwarning rollback;
# flush tables and repopulate
delete from test;
# stored proc that gets called
CREATE PROCEDURE sp_testB()
DECLARE name_var varchar(50);
DECLARE c CURSOR FOR
select name from person_table;
DECLARE EXIT HANDLER FOR SQLSTATE '02000' BEGIN END;
FETCH c INTO name_var;
INSERT INTO test_table(name) values(name_var);
Also is there an alternative to DECLARE EXIT HANDLER FOR SQLSTATE '02000'... to get out of the loop so it doesn't throw an error (ERROR 1329 (02000): No data - zero rows fetched, selected, or processed)
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.