Skip navigation links

MySQL Forums :: Stored Procedures :: Transaction/Rollback in a Stored Procedure which contains nested stored procs


Advanced Search

Transaction/Rollback in a Stored Procedure which contains nested stored procs
Posted by: Alisa Hsiu ()
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()
BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION rollback;
declare exit handler for not found rollback;
declare exit handler for sqlwarning rollback;

START TRANSACTION;

# flush tables and repopulate
delete from test;
call sp_testB();

COMMIT;
END;

# stored proc that gets called
CREATE PROCEDURE sp_testB()
BEGIN
DECLARE name_var varchar(50);
DECLARE c CURSOR FOR
select name from person_table;

OPEN c;
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '02000' BEGIN END;
LOOP
FETCH c INTO name_var;
INSERT INTO test_table(name) values(name_var);
END LOOP;
END;
CLOSE c;
END;

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)

Thanks!

Options: ReplyQuote


Subject Views Written By Posted
Transaction/Rollback in a Stored Procedure which contains nested stored procs 12973 Alisa Hsiu 04/28/2008 01:17PM
Re: Transaction/Rollback in a Stored Procedure which contains nested stored procs 3717 Alisa Hsiu 04/28/2008 05:07PM
Re: Transaction/Rollback in a Stored Procedure which contains nested stored procs 2975 Mohan Kalyan 05/28/2008 06:00AM


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.