Transaction across multiple stored procedures...Posssible?

Question

_{Monday, May 12, 2008 12:27 AM}

Hello All,

I can not figure out how to accomplish the task that I want. So, please help.

I have two tables, Parent and Child tables. For both Tables I have stored procedures for inserting records. I simply pass parameters for field values to stored proecdures and Stored procedures simply insert record with those values. I am first inserting record in Parent table and then in child table via calling their stored procedures one by one.

Now, what I want to do is, after inserting record in parent table successfully, If record can not be inserted in child table successfully then I want to rollback, I also want to undo the insertion operation done in previous parent table's stored procedure call. The problem is, I can not span a Transaction across multiple stored procedure. If I begin a Transaction in a stored procedure for a Parent table, then I have to either roll back or commit that transaction in the same stored procedure. How can I span a transaction across multiple stored procedures, so that I can rollback in the Child Table's stored procedure in such a way that the Record Insertion of the Parent table can be cancelled. What to do...?

Please help, it's urgent.

Thanks to all. 

All replies (4)

_{Monday, May 12, 2008 6:58 AM ✅Answered}

you can try to do this in one sp instead of another sp.

eg:   insert into table1........

      set @Table1Id = (select @@identity)

    insert into table2 ..............values (@table1Id)

and you can keep this sp in a tranaction like this,

 begin transaction
declare @TotalErrors int
set @TotalErrors = 0

 insert into table1........
 set @TotalErrors = @TotalErrors + @@error
      set @Table1Id = (select @@identity)
 set @TotalErrors = @TotalErrors + @@error
    insert into table2 ..............values (@table1Id)
 set @TotalErrors = @TotalErrors + @@error

if @@TotalErrors <> 0
begin
 rollback
 return
end

coMMit

 

 

---

_{Tuesday, May 13, 2008 11:41 AM ✅Answered}

 You can do it. 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
 

 

---

_{Monday, May 12, 2008 1:59 AM}

 

string result = string.Empty;
Database db = DatabaseFactory.CreateDatabase();

using (DbConnection conn = db.CreateConnection())
{
   conn.Open();

   DbTransaction transaction = conn.BeginTransaction();

   try
   {
       db.ExecuteNonQuery(transaction, "STORED_PROC_1", param1, param2, param3);

       db.ExecuteNonQuery(transaction, "STORED_PROC_2", param1, param2, param3);      
 
       transaction.Commit();
   }
   catch
   {
      transaction.Rollback();
   }

   conn.Close();
}

 

---

_{Monday, May 12, 2008 6:06 AM}

hi nKognito,

Thankx for your reply. But I want to accomplish this in SQL Server back-end side and not in .Net front side.