Contecting transactions between two stored procs

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.
 
M

Mary Chipman [MSFT]

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top