How to rollback only single stored procedure action?

grofaty · #1 (**permalink**) 12-24-10, 02:24

Hi,
I would like to create a stored procedure which runs two stored procedures one after another. If first stored procedure runs successful and second fails than I do NOT want to rollback first stored procedure SQLs.

I have written bellow simple sample. But if I execute admin.sp_run and force application in the time second stored procedure is executing (admin.sp2) then SQL from first stored procedure (admin.sp1) is also rollbacked.

Is there any way I can write a admin.sp_run procedure in such a way that if admin.sp2 procedure fails it does not rollback admin.sp1 actions.

Code:

CREATE TABLE ADMIN.TAB (COL1 INT)@

CREATE PROCEDURE ADMIN.SP1
  LANGUAGE SQL
  BEGIN
    INSERT INTO ADMIN.TAB VALUES (1);
  END@

CREATE PROCEDURE ADMIN.SP2
  LANGUAGE SQL
  BEGIN
    UPDATE ADMIN.TAB SET COL1 = 2;
  END@

CREATE PROCEDURE ADMIN.SP_RUN
  LANGUAGE SQL
  BEGIN
     CALL ADMIN.SP1 ();
     CALL ADMIN.SP2 ();
  END
@

CALL ADMIN.SP_RUN()

Thanks

grofaty · #2 (**permalink**) 12-24-10, 03:24

I have found out. Commit statement must be inserted into first procedure like:

Code:

CREATE PROCEDURE ADMIN.SP1
  LANGUAGE SQL
  BEGIN
    INSERT INTO ADMIN.TAB VALUES (1);
    COMMIT;
  END@

Internet Services

Web Development

Digital Marketing

Consumer Tech