January 15, 2013 at 11:41 am
Hi all,
I have (ss 2K8R2) a number of stored procs in the following sequence (split up by logic):
procedure main_p
begin
exec proc 1
exec proc 2
exec proc 3
exec proc 4
exec proc 5
end;
What I am trying to implement is the following:
If proc1 OR proc2 fails, then stop processing and notify me of errors.
If proc1 AND proc2 are executed w/out errors, then proceed with proc3,4,5. If one of them fails, notify me of an error and proceed to the next one.
Will this cover it?
procedure main_p
begin
begin try
proc 1
proc 2
end try
begin catch
begin
-- error notification
exit;
end;
end catch
begin try
proc 3
end try
begin catch
begin
-- log error
end;
end catch
begin try
proc 4
end try
begin catch
begin
-- log error
end;
end catch
begin try
proc 5
end try
begin catch
begin
-- log error
end;
end catch
end;
Thanks,
January 15, 2013 at 2:19 pm
The logic looks OK to me. Is this homework or a real-world scenario? If real-world then consider adding a transaction boundary around proc1 and proc2 so that if proc2 encounters an error work for proc1 is rolled back. Consider the same idea around other sets of proc calls.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 15, 2013 at 6:06 pm
I don't really know if this applies but if you're creating procedure main_p so that you have a cover SP that you can run by SQL agent (on a schedule), you can really just create that error handling logic within the agent process.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply