In some genuine cases, we may experience a failure even when there are no issues with SQL Script or SQL Codes which may be due to large no of databases, high server utilization and parallel process/execution in real time. It can be rectified easily by rerunning it.

For such scenario we need a option to retry/re-execute that particular piece of code for X times (Say 3 times) for successful completion without any manual intervention/validations.

Declare @Counterloop int =0, @IsSuccess int = 0

while (@Counterloop <=3 and @IsSuccess = 0)
Begin
	Begin Try

		--Piece of code for Retry/Re-Execution - START--
		select 1/0
		---EXEC Rexcute_Retry_SP
		--Piece of code for Retry/Re-Execution - END--

		set @IsSuccess=1

	END TRY
	BEGIN Catch
		IF (@Counterloop<=2) Begin
			set @Counterloop=@Counterloop+1
			print 'counter value is'+ cast(@Counterloop as varchar)
		END
		ELSE Throw;
	END Catch
END

Output of Successful Run with no Retries:-

Output of Failure Run After ‘X’ times Retry:-

Happy Learning and Exploring SQL, Please do share this post with your friends if you like it. !!