Retry SQL Code for X times within SQL Script

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 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

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

Output of Successful Run with no Retries:-

Fig 1:- SQL Code executed successfully in the first attempt itself

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

Fig 2:- SQL Code retries for 3 times and exited with Error Message in Final Try

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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s