Contecting transactions between two stored procs

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two sql server stored procedures that use transactions. Both stored
procedures have there own begin transaction , commit and rollback.

Currently I have C# .Net code that executes both of these stored procedures
consecutively. Everything works fine unless the second stored procedure has
an error and rolls itself back. The resulting issue is that the first stored
procedure is already committed, but it also should have been rolled back
because the second one fails.

One way I could fix this, of course, is to merge the two into a single
stored procedure. However, because the the second stored procedure is used by
other parts of our system, I am hesitant.

Can someone tell me of there is another way to handle this? Is it possible
to use transactions via ADO.net (SqlTransaction tran =
conn.BeginTransaction();
) that can encapsulate the two stored procedures?


You input will be appreciated.
 
Why not create a third stored procedure that performs the operations
in the correct order and just leave the other two alone? This way you
don't break other parts of your app and you avoid adding another layer
to debug in your application.

-mary
 
Back
Top