Unanswered: How to rollback only single stored procedure action?
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.
CREATE TABLE ADMIN.TAB (COL1 INT)@
CREATE PROCEDURE ADMIN.SP1
INSERT INTO ADMIN.TAB VALUES (1);
CREATE PROCEDURE ADMIN.SP2
UPDATE ADMIN.TAB SET COL1 = 2;
CREATE PROCEDURE ADMIN.SP_RUN
CALL ADMIN.SP1 ();
CALL ADMIN.SP2 ();