Xử lý transaction lồng nhau

SET XACT_ABORT ON;
 
DECLARE @_IsUseTrans BIT = 0
DECLARE @_SavePoint NVARCHAR(32) = 'SavePoint_Name';
 
DECLARE @errNumber INT, @errSeverity INT, @errState INT,
	@errProc NVARCHAR(256), @errLine INT, @errMessage NVARCHAR(MAX);
 
BEGIN TRY
	IF @@TRANCOUNT = 0
	BEGIN
		BEGIN TRANSACTION;
		SET @_IsUseTrans = 1;
	END ELSE
		SAVE TRANSACTION @_SavePoint;
 
	/* T-SQL */
 
	IF @_IsUseTrans = 1
		COMMIT TRANSACTION;
END TRY
BEGIN CATCH
	SELECT @errNumber = ERROR_NUMBER(),
		@errSeverity = ERROR_SEVERITY(),
		@errState = ERROR_STATE(),
		@errProc = ERROR_PROCEDURE(),
		@errLine = ERROR_LINE(),
		@errMessage = ERROR_MESSAGE();
 
	IF XACT_STATE() = -1
	BEGIN
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION;
	END ELSE
	BEGIN
		IF @_IsUseTrans = 1
		BEGIN
			IF @@TRANCOUNT > 0
				ROLLBACK TRANSACTION;
		END
		ELSE
			IF @@TRANCOUNT > 0
				ROLLBACK TRANSACTION @_SavePoint;
	END
 
	RAISERROR('%s (Proc: %s, Line: %d)', @errSeverity, @errState, @errMessage, @errProc, @errLine);
	GOTO DROP_TEMPORARY_TABLE;
END CATCH