Reviewing Stored Procedure template for handling transaction? I've prepare a template for SQL Server stored procedures which will have Insert, delete, Update transaction.

I have a stored procedure to log error in catch block in a table.

How can I improve my stored procedure to handle transactional error or any kind of error
where stored procedure should not give error on screen.


Can this be improved to handle transaction in proper way or it sufficient?
First Begin tran block or Begin Try block?
Rollback in catch block if @@trancount &gt; 0 sufficient?
Use of Xact_abort on in every Insert, delete, update transaction procedure is ok?


Code: 

ALTER PROCEDURE [dbo].[DB_INSERT_MY_LOG]
(@Param1 varchar(30))
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE          @ErrNumber         NVARCHAR(100)   
    ,@ErrDescription    NVARCHAR(4000)  
    ,@ErrorProcedure    NVARCHAR(200)
    ,@ErrState          INT             
    ,@ErrSeverity       INT             
    ,@ErrLine           INT             
    ,@ErrTime           DATETIME

    BEGIN TRAN 
    BEGIN TRY   
                   --Insert or Update or Delete or (Insert &amp; Update together and vice  versa) &amp; multiple insert into different tables.
    COMMIT TRAN
    END TRY

    BEGIN CATCH

    IF (@@TRANCOUNT &gt; 0)  
    ROLLBACK TRAN

    SET @ErrNumber          = ERROR_NUMBER()
    SET @ErrDescription = ERROR_MESSAGE()
    SET @ErrorProcedure    = ERROR_PROCEDURE()
    SET @ErrState           = ERROR_STATE()
    SET @ErrSeverity        = ERROR_SEVERITY()
    SET @ErrLine            = ERROR_LINE()
    SET @ErrTime            = GETDATE()

    EXEC    MyErrorLogInsert    
    @ErrorNumber                = @ErrNumber
    ,@ErrorDescription          = @ErrDescription
    ,@ErrorProcedure            = 'DB_INSERT_MY_LOG'
    ,@ErrorState                = @ErrState
    ,@ErrorSeverity             = @ErrSeverity
    ,@ErrorLine                 = @ErrLine
    ,@ErrorTime                 = @ErrTime

    END CATCH

    SET NOCOUNT OFF;
    SET XACT_ABORT OFF;
It might be worth looking at XACT_STATE instead of @@TRANCOUNT.

From BOL:


  Both the XACT_STATE and @@TRANCOUNT functions can be used to detect
  whether the current request has an active user transaction.
  @@TRANCOUNT cannot be used to determine whether that transaction has
  been classified as an uncommittable transaction.


So there may be some cases where, even though there was an error, the transaction should still be committed to the database.

IF (XACT_STATE()) = -1
BEGIN
    PRINT 'The transaction is in an uncommittable state.' +
          ' Rolling back transaction.'
    ROLLBACK TRANSACTION;
END;

-- Test whether the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
    PRINT 'The transaction is committable.' + 
          ' Committing transaction.'
    COMMIT TRANSACTION;   
END;


Of course, it's entirely up to you whether you want to commit any transaction where an error was raised, even if it is committable... Just because you can, doesn't mean it's always a good idea!