I'm not exactly sure what you are looking to do, it would be helpful if you would provide some DDL to have a look .
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