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