DECLARE @retry INT = 3;
DECLARE @waitForTime CHAR(8) =  '00:00:01'	-- 1 SECOND
DECLARE @ErrorMessage nvarchar(4000);
DECLARE @ErrorSeverity int;
DECLARE @ErrorState int;
DECLARE @TranCount int  = @@trancount;  
DECLARE @TranName varchar(32)

BEGIN TRY
	WHILE @retry > 0
	BEGIN
		BEGIN TRY
			IF @TranCount > 0
			BEGIN
				SET @TranName = 'CS' + CAST( NEWID() AS VARCHAR(36))	-- create almost unique save tran name
				SAVE TRANSACTION @TranName;
			END
			ELSE
				BEGIN TRANSACTION;
			
			/******************************************************************************
			-- INSERT / Update data...from which we occassionally get 1205 deadlocks errors
			******************************************************************************/
			IF @TranCount = 0 
				COMMIT TRANSACTION;
	
			SET @retry = 0;

		END TRY
		BEGIN CATCH 
			DECLARE @ErrorNumber INT= ERROR_NUMBER();
			DECLARE @XACTState INT= XACT_STATE();

			SET @ErrorMessage = ERROR_MESSAGE();	-- since this is inner catch only use sql error
			SET @ErrorSeverity = ERROR_SEVERITY();
			SET @ErrorState = ERROR_STATE();		

			IF @XACTState <> -1 --If the transaction is still valid then roll back to the savepoint
				AND @TranCount > 0 -- if we started a savepoint and this is deadlock
				ROLLBACK TRANSACTION @TranName;
			ELSE
				IF @@TRANCOUNT > 0  	-- we started the transaction, so roll it back or it was doomed transaction
					ROLLBACK TRANSACTION;

			IF  ((@TranCount > 0 AND @XACTState <> -1) OR @TranCount = 0)
				AND @ErrorNumber = 1205  -- we can retry if the transaction was not doomed and it was a deadlock error
			BEGIN
				-- If Deadlock Error, reduce @retry count for next retry
				SET @retry = @retry - 1;
				PRINT 'Deadlock detected, retrying update: ' + @ErrorMessage
				WAITFOR DELAY @waitForTime; -- 1 second
			END
			ELSE
			BEGIN
				-- If a different error exit retry WHILE Loop
				SET @retry = -1;

				RAISERROR (@ErrorMessage, -- Message text.
							@ErrorSeverity, -- Severity.
							@ErrorState -- State.
							);

			END

		END CATCH;

	END; -- End WHILE loop.

	-- some more code to deal with return values

	RETURN 0
END TRY
BEGIN CATCH
	IF @@TRANCOUNT > 0 
		ROLLBACK TRANSACTION -- Rollback Tran

	SELECT 
        @ErrorMessage = ERROR_MESSAGE() + ' Error occurred in ''' + ERROR_PROCEDURE()  + ''' at line #' + CAST(ERROR_LINE() AS varchar(30)) + '.',
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
			  )
	RETURN -1
END CATCH


GO

DECLARE @retry INT = 3;
DECLARE @waitForTime CHAR(8) =  '00:00:01'	-- 1 SECOND
DECLARE @ErrorMessage nvarchar(4000);
DECLARE @ErrorSeverity int;
DECLARE @ErrorState int;
DECLARE @TranCount int  = @@trancount;  
DECLARE @TranName varchar(32)

BEGIN TRY
	WHILE @retry > 0
	BEGIN
		BEGIN TRY
			IF @TranCount > 0
			BEGIN
				SET @TranName = 'CS' + CAST( NEWID() AS VARCHAR(36))	-- create almost unique save tran name
				SAVE TRANSACTION @TranName;
			END
			ELSE
				BEGIN TRANSACTION;
			
			/******************************************************************************
			-- INSERT / Update data...from which we occassionally get 1205 deadlocks errors
			******************************************************************************/
			IF @TranCount = 0 
				COMMIT TRANSACTION;
	
			SET @retry = 0;

		END TRY
		BEGIN CATCH 
			DECLARE @ErrorNumber INT= ERROR_NUMBER();
			DECLARE @XACTState INT= XACT_STATE();

			SET @ErrorMessage = ERROR_MESSAGE();	-- since this is inner catch only use sql error
			SET @ErrorSeverity = ERROR_SEVERITY();
			SET @ErrorState = ERROR_STATE();		

			IF @XACTState <> -1 --If the transaction is still valid then roll back to the savepoint
				AND @TranCount > 0 -- if we started a savepoint and this is deadlock
				ROLLBACK TRANSACTION @TranName;
			ELSE
				IF @@TRANCOUNT > 0  	-- we started the transaction, so roll it back or it was doomed transaction
					ROLLBACK TRANSACTION;

			IF  ((@TranCount > 0 AND @XACTState <> -1) OR @TranCount = 0)
				AND @ErrorNumber = 1205  -- we can retry if the transaction was not doomed and it was a deadlock error
			BEGIN
				-- If Deadlock Error, reduce @retry count for next retry
				SET @retry = @retry - 1;
				PRINT 'Deadlock detected, retrying update: ' + @ErrorMessage
				WAITFOR DELAY @waitForTime; -- 1 second
			END
			ELSE
			BEGIN
				-- If a different error exit retry WHILE Loop
				SET @retry = -1;

				RAISERROR (@ErrorMessage, -- Message text.
							@ErrorSeverity, -- Severity.
							@ErrorState -- State.
							);

			END

		END CATCH;

	END; -- End WHILE loop.

	-- some more code to deal with return values

	RETURN 0
END TRY
BEGIN CATCH
	IF @@TRANCOUNT > 0 
		ROLLBACK TRANSACTION -- Rollback Tran

	SELECT 
        @ErrorMessage = ERROR_MESSAGE() + ' Error occurred in ''' + ERROR_PROCEDURE()  + ''' at line #' + CAST(ERROR_LINE() AS varchar(30)) + '.',
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
			  )
	RETURN -1
END CATCH


GO