What is the correct template to use with try catch and transactions?

Question

I am providing 4 examples below. My question is - which is the appropriate template to use when dealing with try/catch and transactions.

Example 1

TSQL Try / Catch within Transaction or vice versa?

The accepted answer for the above has the following structure:

BEGIN TRY    
  BEGIN TRANSACTION SCHEDULEDELETE
    // do something
  COMMIT TRANSACTION SCHEDULEDELETE
  PRINT 'Operation Successful.'
END TRY

BEGIN CATCH 
  IF (@@TRANCOUNT > 0)
   BEGIN
      ROLLBACK TRANSACTION SCHEDULEDELETE
      PRINT 'Error detected, all changes reversed'
   END 
    //may be print/log/throw error
END CATCH 

Example 2

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-ver15#b-using-trycatch-in-a-transaction

The structure given is:

BEGIN TRANSACTION;  
BEGIN TRY  
    -- Generate a constraint violation error.  
    DELETE FROM Production.Product  
    WHERE ProductID = 980;  
END TRY  
BEGIN CATCH  
    //may be print/log/throw error
  
    IF @@TRANCOUNT > 0  
        ROLLBACK TRANSACTION;  
END CATCH;  
  
IF @@TRANCOUNT > 0  
    COMMIT TRANSACTION;  
GO  

Example 3

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-ver15#c-using-trycatch-with-xact_state

Microsoft also recommends using XACT_ABORT.

SET XACT_ABORT ON;  
  
BEGIN TRY  
    BEGIN TRANSACTION;  
    //do something    
    COMMIT TRANSACTION;  
END TRY  
BEGIN CATCH  
    -- Test whether the transaction is uncommittable.  
    IF (XACT_STATE()) = -1  
    BEGIN  
        PRINT  
            N'The transaction is in an uncommittable state.' +  
            'Rolling back transaction.'  
        ROLLBACK TRANSACTION;  
    END;  
  
    -- Test whether the transaction is committable.
    -- You may want to commit a transaction in a catch block if 
    -- you want to commit changes to statements that ran prior 
    -- to the error.
    IF (XACT_STATE()) = 1  
    BEGIN  
        PRINT  
            N'The transaction is committable.' +  
            'Committing transaction.'  
        COMMIT TRANSACTION;     
    END;  
END CATCH;  

Example 4

This is what I use personally

BEGIN TRY
  BEGIN TRANSACTION
    //do something    
  COMMIT TRANSACTION
END TRY

BEGIN CATCH
  IF (@@TRANCOUNT > 0)
  BEGIN
   ROLLBACK TRANSACTION
  END ;

  //may be print/log/throw error

END CATCH

To summarize:

1. Example 1 - Transaction is inside the try block
2. Example 2 - Try is inside the transaction block, COMMIT is after the catch block
3. Example 3 - Transaction is inside the try block with xact_abort ON functionality
4. Example 4 - Transaction is inside the try block, COMMIT is inside and as the last line of the try block

Is my approach (example 4) the correct way to handle try/catch and transaction in SQL. If not, then why not and which example should I use to refactor my code from example 4?

Answer 1

according to the documentation

TRY...CATCH constructs do not trap the following conditions:

• Warnings or informational messages that have a severity of 10 or lower.

• Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY...CATCH will handle the error.

• Attentions, such as client-interrupt requests or broken client connections.

• When the session is ended by a system administrator by using the KILL statement.

The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY...CATCH construct:

• Compile errors, such as syntax errors, that prevent a batch from running.

• Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

• Object name resolution errors

These errors are returned to the level that ran the batch, stored procedure, or trigger.

so taking this fact into account, let's consider this examples.

Exaple 1: If we try to delete a row from a non-existent table, the session will remain with an open transaction, which will preserve resource locks and prevent VLFs from being reused in the transaction log for a simple recovery model.

BEGIN TRY    
  BEGIN TRANSACTION SCHEDULEDELETE
    Delete From dbo.NoExists Where ID=1
  COMMIT TRANSACTION SCHEDULEDELETE
END TRY

BEGIN CATCH 
  IF (@@TRANCOUNT > 0)
   BEGIN
      ROLLBACK TRANSACTION SCHEDULEDELETE
   END 
END CATCH

Invalid object name "dbo.NoExists".

Select @@TRANCOUNT
-- 1

Example 2: The same problem as in example 1.

BEGIN TRANSACTION;  
BEGIN TRY  
    DELETE FROM dbo.NoExists Where ID=1  
END TRY  
BEGIN CATCH    
    IF @@TRANCOUNT > 0  
        ROLLBACK TRANSACTION;  
END CATCH;  

IF @@TRANCOUNT > 0  
    COMMIT TRANSACTION;  

Invalid object name "dbo.NoExists".

Select @@TRANCOUNT
-- 1

Example 3: In this example, XACT_ABORT ON will rollback the transaction if CATCH block doesn't handle this error and, therefore, doesn't rollback the transaction.

SET XACT_ABORT ON;  

BEGIN TRY  
    BEGIN TRANSACTION;  
        DELETE FROM dbo.NoExists Where ID=1   
    COMMIT TRANSACTION;  
END TRY  
BEGIN CATCH  
    -- Test whether the transaction is uncommittable.  
    IF (XACT_STATE()) = -1  
    BEGIN  
        PRINT  
            N'The transaction is in an uncommittable state.' +  
            'Rolling back transaction.'  
        ROLLBACK TRANSACTION;  
    END;  

    IF (XACT_STATE()) = 1  
    BEGIN  
        PRINT  
            N'The transaction is committable.' +  
            'Committing transaction.'  
        COMMIT TRANSACTION;     
    END;  
END CATCH;

Invalid object name "dbo.NoExists".

Select @@TRANCOUNT
-- 0