June 22, 2009 at 11:42 am
Hi All,
I have a procedure the calls another procedure internally.After completion of Inner Procedure; the outer procedure performs some actions on the data updated by the inner procedure.
I have Two scenario:
1) If somethings goes wrong in inner proc nothing should get committed in inner procedure and also in outer procedure
2) Even though the inner procedure is successful, if something goes wrong in the outer procedure (after completion of inner procedure); then the data updated by inner query should also rollback
CREATE PROCEDURE usp_MyProc1
AS
BEGIN TRY
BEGIN TRAN OUTER
---[Start]usp_MyProc2
BEGIN TRY
BEGIN TRAN INNER
COMMIT TRY INNER
END TRY
BEGIN CATCH
ROLLBACK TRAN INNER
END CATCH
---[End]usp_MyProc2
COMMIT TRAN OUTER
END TRY
BEGIN CATCH
ROLLBACK OUTER
END CATCH
Does the above was of using TRY-CATCH will help in achieving my requirement of committing things when both the inner and outer proc are sucessfully executed Or Rollback if either of the two Stored Proc has some error while executing
Kindly suggest
Thanks in advance for your time and suggestions
June 22, 2009 at 12:46 pm
I'm not sure about your second question, but for the first - you can use and output parameter on your inner stored procedure which can indicate whether it completed successfully.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply