MS SQL sauberes Transaction handling

[BaseBallBatBoy]

Hallo!

Ich wollte mal in die Runde fragen, wie Ihr bei MS SQL Transaction handling am besten aufsetzen würdet?
Mein Skeletton welches ich bislang habe ist folgendes, aber für Verbesserungsvorschläge bin ich offen:

USE [My_DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
 * Author:		abc
 * Date:		01.01.1900
 * Version:		1.0
 * Desc:		Skeletton
*/
CREATE PROCEDURE [dbo].[my_SP_Name]
	@Output_on INT 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON

	-- SET XACT_ABORT ON will cause the transaction to be uncommittable
	-- when the constraint violation occurs. 
	SET XACT_ABORT ON


	BEGIN TRY
    	BEGIN TRANSACTION
			/*
			some work to do...
			*/
			
		-- Transaction State (XACT_STATE()) 
		-- If 1, the transaction is committable.
		IF (@@TRANCOUNT > 0 AND XACT_STATE() = 1) 
			COMMIT TRANSACTION
	
		IF (@Output_on = 1)
			EXEC my_SP_write_to_log 'my_SP_Name ENDE!';

	END TRY
	BEGIN CATCH
		-- Transaction State (XACT_STATE()) 
		-- If -1, the transaction is uncommittable and should be rolled back.
		IF (@@TRANCOUNT > 0AND XACT_STATE() = -1) 
			ROLLBACK TRANSACTION
			INSERT INTO my_error_log (Zeitpunkt, Source, MSG, MSG_NR, Line_Nr) 
			VALUES (GETDATE(), ERROR_PROCEDURE() , ERROR_MESSAGE(), ERROR_NUMBER(), ERROR_LINE());
		
		IF (@Output_on = 1)
			EXEC my_SP_write_to_log 'my_SP_Name ERROR!';
			
	END CATCH
END