CODEWITHSUNDEEP
.netsqlsql-server
trousyt
trousyt

Reputation: 356

Wrapping a transacted Stored Procedure inside a transaction

I've been trying to get this working for a while.

I have a stored procedure (we'll call it SPA) written by another developer that does multiple inserts into the database and all of those inserts are wrapped inside a transaction in the stored procedure. I also have another stored procedure that doesn't use transactions (call it SPB).

From my .NET code, I need to wrap both of these stored procedures in a transaction as well to make sure that if SPB isn't successful, then all of SPA is rolled back. Unfortunately this isn't working for me. The error I get is:

Cannot roll back SALE. No transaction or savepoint of that name was found.
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 2.

I've confirmed that ROLLBACK or COMMIT is always being called inside SPA before exiting. My .NET code is pretty simple:

try {
    conn.Open();
    trans = conn.BeginTransaction();
    prod.Connection = conn;
    prod.Transaction = trans;

    // Execute SPA
    // Execute SPB

} catch (Exception ex) {
    trans.Rollback();
} finally {
    conn.Close();
}

If I take .NET out of the equation and simply use SSMS to wrap the SP like so I get the same error message.

BEGIN TRAN
DECLARE @return_value int
EXEC    @return_value = [dbo].[spSPA] [...]
SELECT  'Return Value' = @return_value
COMMIT TRAN

Any ideas?

EDIT:

SPA looks something like:

BEGIN TRY
BEGIN TRAN SALE

IF SomeCondition
  DoSomething
ELSE
  ROLLBACK TRAN SALE
  RETURN 100

IF SomeCondition
  DoSomething
ELSE
  ROLLBACK TRAN SALE
  RETURN 200

...

COMMIT TRAN SALE
RETURN 0

END TRY
BEGIN CATCH
  IF XACT_STATE() <> 0
    ROLLBACK TRAN SALE
END CATCH

Upvotes: 1

Views: 2226

Answers (1)

trousyt
trousyt

Reputation: 356

I finally discovered my issue. I'm getting the warning Cannot roll back SALE. No transaction or savepoint of that name was found. because if you have a nested transaction, on rollback you MUST specify the name of the outermost transaction. Of course I wouldn't know what the name of that transaction is.

The workaround is to use savepoints like described on TechNet. Therefore I have to make checks to see if there is an existing transaction and if so, use savepoints. Otherwise, create my own:

DECLARE @TRANCOUNT int
SET @TRANCOUNT = @@TRANCOUNT

IF @TRANCOUNT = 0 BEGIN TRAN
ELSE SAVE TRANSACTION SALE

Upvotes: 1

Related Questions