How do I use transactions over multiple stored pro

Question

Can you start a transaction in one stored procedure and then roll it back or commit it in a nested procedure?

Answer 1

You can't commit it in a nested procedure, but starting a transaction will wrap all nested procedures within it. So the transaction is good for all stored procedures nested within the transaction. In distributed transactions, data integrity even crosses machine boundaries.

http://msdn.microsoft.com/en-us/library/ms188929(v=SQL.90).aspx

Answer 2

Yes, it is possible. With programming languages like C#, when you pass the connection and transaction object with the command. if anything is caught as wrong than rollback the transaction:

   string customerConnection = "Connection";
        string query = "insert into temp values ('Data2','data1','data2','data3')";
        string query2 = "update tempcst set data = 'Hello data'";

        SqlConnection myConnection = new SqlConnection(customerConnection);
        myConnection.Open();


        SqlTransaction myTrans = myConnection.BeginTransaction();

 Try{

        int result = executeNonQuery(query, myConnection, myTrans, "");
        i = executeNonQuery(query2, myConnection, myTrans, "");
   myTrans.Commit();}



  catch{
        myTrans.Rollback();
        myConnection.Close();
 }

Answer 3

Commit and rollback have different effects

• COMMIT decrements @@TRANCOUNT
• ROLLBACK pushes it back to zero

This happens because SQL Server does not really support nested transactions.

If you commit or rollback in a nested stored proc (not transaction), then you'll generate error 266 because of a @@TRANCOUNT mismatch on start and entry

The rollback issue can be resolved by using SET XACT_ABORT ON which is "auto rollback" (simply) and suppresses error 266.

The commit issue... you can't as such. However, you can control where it happens by noting @@TRANCOUNT on stored proc entry and committing only if zero.

For correct transaction handling, see my answers here please: Nested stored procedures containing TRY CATCH ROLLBACK pattern? and Have I to count transactions before rollback one in catch block in T-SQL?

Answer 4

You should pair up your BEGIN TRAN and COMMITs in the same SPROC

If you then call another SPROC which also has a transaction, subsequent BEGIN TRAN / COMMIT TRAN pairs will increment and decrement @@Trancount respectively.

The transaction is committed on the 'last' COMMIT TRAN (@@Trancount = 1)

However, any ROLLBACK will always roll back the transaction.

MSDN has a good explanation.