January 22, 2016 at 9:49 am
Hi,
I have the following master stored procedure where execute other store procedures one by one and send an emails after complete each one and when it fail it send an email too and stop there. Now, i want to continue to the next alert store procedure if failed but i don't know how to do that.
January 22, 2016 at 9:59 am
As I do not have a couple of hours to burn today, I did not go through all of your code 😎
However, I suggest that you look into SQL Server's TRY ...CATCH functionality (eg, link here), which should allow you to trap errors without (necessarily) terminating execution.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 22, 2016 at 10:02 am
I'm using try and catch
January 22, 2016 at 10:08 am
If I understand what you're trying to do correctly, try this:
while(@min_AlertProcedure IS NOT NULL AND @min_AlertProcedure <= @max_AlertProcedure)
begin
begin try
--call your procs
end try
begin catch
--handle the error
end catch
end
January 22, 2016 at 10:22 am
It failed
January 22, 2016 at 10:30 am
To be honest, I'm not going to go through your code and find the issue with that. But if you have a loop and it makes a bunch of calls, and even when one of them fails, you want it to continue with the rest of the calls, the outline I gave you is what you're looking for. Put the try/catch in the loop rather than outside of it.
As for the infinite loop. you have to review where you're setting your variables the loop is based on.
January 22, 2016 at 11:58 am
I quickly scanned the posted code. The problem is that the layout is like this:
WHILE (condition)
BEGIN;
BEGIN TRY
-- Do something that might fail
-- Find next row to process
END TRY
BEGIN CATCH
-- Handle error
END CATCH
END; -- Ends while loop
If you get a failure, control passes to the CATCH block. So the last part of the TRY block, where the next row is read, is never reached.
Try rearranging like this:
WHILE (condition)
BEGIN;
BEGIN TRY
-- Do something that might fail
END TRY
BEGIN CATCH
-- Handle error
END CATCH
-- Find next row to process
END; -- Ends while loop
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply