SQL TRY-CATCH WITH TRANSACTION

trycatchtran.sql

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[pr_ins_test]
@CompanyID INT
AS

SET NOCOUNT ON

BEGIN

	DECLARE @PreviousConfigID INT
	
	BEGIN TRY
		BEGIN TRANSACTION MYTRAN; -- Give the transaction a name
		SELECT 1/0  -- Generates divide by zero error causing control to jump into catch

		PRINT '>> COMMITING'
		COMMIT TRANSACTION MYTRAN;
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
		BEGIN 
			PRINT '>> ROLLING BACK'
			ROLLBACK TRANSACTION MYTRAN;
			
			THROW
		END
	END CATCH
END