1

I have a TRY/CATCH block with a transaction that I would like to rollback based upon the results of executing a stored procedure.

BEGIN TRY
     BEGIN TRAN
          INSERT Record
          --Business validation
          EXEC StoredProcedure  --This should throw error
          PRINT 'Commit Tran'
          COMMIT TRAN
END TRY
BEGIN CATCH
     PRINT 'In CATCH Block'
     ROLLBACK TRAN;
 END CATCH
 PRINT 'After END CATCH'

In my testing, the INSERT Record is committed, the stored procedure fails as expected, the PRINT 'COMMIT Tran' is NOT printed, and the code gets sent to the CATCH block with the following error: "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION."

If I replace the EXEC StoredProcedure with an INSERT that's designed to fail, the original INSERT does not commit, and the ROLLBACK in the CATCH block runs properly with no error.

So the question is how does EXEC StoredProcedure affect transactions, and how can I fix this?

bbeck
  • 13
  • 3
  • Does this https://stackoverflow.com/questions/180075/executing-a-stored-procedure-inside-begin-end-transaction help at all? – paneerakbari Nov 12 '21 at 21:10
  • What are the values of `@@TRANCOUNT` at each step of your process? Try adding `PRINT` statements to capture those - it may help illustrate what's happening – paneerakbari Nov 12 '21 at 21:15
  • You say "the INSERT Record is committed" - can you show in code where you are determining that? Also, how is your stored procedure erroring: Does it `THROW` or `RAISERROR`, are there transaction control statements in that proc? – paneerakbari Nov 12 '21 at 21:32
  • @paneerakbari 1) `@@TRANCOUNT`says 1 transaction after the INSERT Record, 0 transaction in the CATCH block. 2) I say "the INSERT Record is committed" because the record remains in the table after the procedure fails. 3) The stored procedure errors with `RAISERROR`, and no transaction controls in that procedure – bbeck Nov 12 '21 at 23:14

1 Answers1

0

Your StoredProcedure is ending the transaction, either explicitly or implicitly. Perhaps it is calling some other procedure which does have a commit or perhaps it is doing a straight forward ‘commit` somewhere

Here is a demo using your example which does not implicitly/explicitly https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=1d02ee0255aa6c7131dd7e300704bab5

Andrew Sayer
  • 2,296
  • 1
  • 7
  • 9