July 22, 2022 at 6:00 pm
Hello friends,
I am working on some piece of code and where I am having a dynamic SQL to run in iterations. For every iteration I want to have a transaction which will commit if the dynamic SQL is complete or else will retry for say 2 or 3 times before failure and eventually rollback. I know usual begin tran...rollback stuff but having it with retry logic with dynamic SQL is little difficult for me to understand. Here's the query looks like
Declare @miniteration int =1, @maxiteration int=10, @col1 int
While @miniteration <=@maxiteration
Begin
Select @col1 = col1 from temp where sno=@miniteration
@sql='update t set Val=10 from dbo.temp where col1 ='+@col1
Select @miniteration=@miniteration+1
End
The code is somewhat like this. I want to put the dynamic SQL under transaction for each iteration and if there's any failure then retry it for say 3 times before rolling back. Please guide.
Thanks
July 23, 2022 at 6:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
July 25, 2022 at 2:35 pm
Retry logic in SQL is difficult as there may be errors that kills your session. You could use try catch but it's still tricky
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply