Does EXEC Commit a Transaction in TRY/CATCH block?-CodePudding

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?

CodePudding user response：

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