May 11, 2009 at 5:03 pm
I have some questions about error handling in a stored procedure. First of all, this stored procedure is dynamic one where based on input parameters conditions I build SQL string (let's call it primary scope), and the secondary scope is when I send it to SQL Server engine for execution. Due to complexity this procedure has multiple statements, but none of them like insert/update/delete.
My questions are:
1. Should I build error handling on primary scope or the secondary scope, or both ?
2. What methodology is better to use: checking each statement for @@error or to apply try/catch for whole thing ?
What other hurdles should I be aware of ?
Thanks.
May 11, 2009 at 10:09 pm
I think that error handling to be done on the scope of sp. I cannot understand the Primary scope or Secondary scope in your case.
It would be better to go for try and catch.
Bigin Try
-- Your Statements
End Try
Begin Catch
--Raise error manually to get it handeled on the front end (if required)
End Catch
In case of @@error, it is cleared and reset on each statement executed, check it immediately following the statement validated, or save it to a local variable that can be checked later. This makes it a little un-usable in a complex scope.
May 11, 2009 at 11:09 pm
SQL Guy (5/11/2009)
[/code]
Safest way is to have error handling on both.
2. What methodology is better to use: checking each statement for @@error or to apply try/catch for whole thing ?
TRY..CATCH, definitely. However, whether you cover the whole batch with one TRY..CATCH or have several is dependent on you code logic and your error reporting needs.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply