Is it correct/best practice to have the TRY/CATCH block inside the transaction or should the transaction be inside the TRY block?

Technology CommunityCategory: T-SQLIs it correct/best practice to have the TRY/CATCH block inside the transaction or should the transaction be inside the TRY block?
VietMX Staff asked 3 years ago

Only open a transaction once you are inside the TRY block and just before the actual statement, and commit it straightaway. Do not wait for your control to go to the end of the batch to commit your transactions.

If something goes wrong while you are in the TRY block and you have opened a transaction, the control will jump to the CATCH block. Simply rollback your transaction there and do other error handling as required.

BEGIN TRY

  BEGIN TRANSACTION SCHEDULEDELETE
    DELETE   -- delete commands full SQL cut out
    DELETE   -- delete commands full SQL cut out
    DELETE   -- delete commands full SQL cut out
 COMMIT TRANSACTION SCHEDULEDELETE
    PRINT 'X rows deleted. Operation Successful Tara.' --calculation cut out.
END TRY

BEGIN CATCH 
  IF (@@TRANCOUNT > 0)
   BEGIN
      ROLLBACK TRANSACTION SCHEDULEDELETE
      PRINT 'Error detected, all changes reversed'
   END 
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage
END CATCH