Stored Procedure with in stored procedure

[SivaKumar A]

Hi,

I am doing an Human resource management module in that, i am using the 4 stored procedure to process the employee attendance, and calling this 4 procedures with in another procedure called sp_master_attendance_process.


DROP PROCEDURE IF EXISTS sp_master_attendance_process;
CREATE PROCEDURE sp_master_attendance_process(pmDate DATE)
BEGIN

CALL sp_attendance_procedure1(pmDATE,pmOutResult1);

IF pmOutResult1 = 1 THEN
CALL sp_attendance_procedure2(pmDATE,pmOutResult2);
END IF;

IF pmOutResult2 = 1 THEN
CALL sp_attendance_procedure3(pmDATE,pmOutResult3);
END IF;

IF pmOutResult3 = 1 THEN
CALL sp_attendance_procedure4(pmDATE,pmOutResult4);
END IF;

COMMIT;
END;

here, pmOutResult1,pmOutResult2,pmOutResult3,.. is and OUT parameter which return the 1 or 0 based the success or failure from sp_attendance_procedure1,sp_attendance_procedure2,...

the Issue is that if procedure sp_attendance_procedure3 is get an error then the sp_attendance_procedure1 and sp_attendance_procedure2 not roll backed so is there any way to do that.

I want to roll back all the changes if any procedure get all error.

please help for this work around.

Thanks in advance,
shiva