Transaction across multiple stored procedures



If you begin a transaction in calling stored procedure, it would be valid in the called stored procedure as well. All you have to make sure is, if the insert fails, return an error code from the called(child) stored procedure and handle it in the calling stored procedure. Or Handle the rollback in the child itself and raise an error in the catch block. Here is a skeleton:

Create procedure [dbo].[parent]
as
Begin Transaction
Begin Try
    Exec Child
End Try
Begin Catch
    If @@Trancount > 0
        RollBack
End Catch
Commit


Create procedure [dbo].[Child]
as
Begin Transaction
Begin Try
    --Do inserts here
End Try
Begin Catch
    If @@Trancount > 0
        RollBack
    RAISERROR('Error Occured',16,1)
End Catch
Commit

No comments: