Managing Transactions in Stored Procedure (Nested)


  • Hi Everyone:

    I have a master sp that calls 5 different sps. I would like to incorporate transaction(COMMIT and ROLLBACK) into my master sp. If do that, is that enough, or do I need to add some transaction code to the 5 sps that are being called. I would appreciate if you provide me with code, syntax and steps on how to do this for a specific situation like mine. I have read some articles on nested sps and transactions, but most are very complex examples, I just need a simple approach/advise. Thanks.


    Tuesday, January 02, 2007 4:38 PM

All replies

  • Rollback and Commit action should apply to your nested stored procedure calls. When you call a stored procedure within a transaction it executes within the context of that transaction. However, be wary about errors from the stored procedures you are calling from the master stored procedure. Review error handling within stored procedures to make sure you are equipt to handle nested errors and apply the proper transaction method.

    Wednesday, May 09, 2007 2:15 PM