January 15, 2003 at 1:45 pm
What's the preferred method for error handling in stored procedures? Most of our user interfacing is extranet/intranet based, using a combination of .asp, .html, ole db, some xml and slowly migrating to .NET.
Our stored procedures handle a variety of activities (insert, update and delete) and we'd like to standardize the way we handle errors.
Edited by - richardhack on 01/15/2003 1:47:31 PM
January 15, 2003 at 2:04 pm
I do this:
IF @@ERROR <> 0 GOTO error_handler
after every SQL statement that modifies DB (CREATE, DROP, INSERT, UPDATE, DELETE, TRUNCATE, etc.).
At the end of each SP I have the error_handler label and appropriate code:
RETURN ( 0 )
error_handler:
RETURN ( -1 )
In the parameter validation section I raise error if aparameter is invalid and jump to error_handler:
IF <parameter invalid>
BEGIN
RAISERROR( 'pr_XYZ ERROR: Parameter @abc value %s is invalid.', 16, 1, @abc)
GOTO error_handler
END
Cheers,
Michael
Edited by - mromm on 01/15/2003 2:06:48 PM
January 18, 2003 at 6:19 am
hi guys....
SQL Server error generation and handling is very inconsistent and unpredictable. "IF @@ERROR..." construct is not a guarantee that you will trap the error because some errors cut the transaction before the error test is executed.
Don't just take my word for it; search for other threads using "error handler" keywords.
Edited by - bani on 01/18/2003 06:22:21 AM
"The way to get things done is not to mind who gets the credit for doing them." - Benjamin Howett
January 18, 2003 at 11:36 am
IF @@ERROR should be placed after EACH statement involving changes to the database. If your error handling practice is consistent and correct, it will work well. I am talking from my own experience with SQL Server versions 4.21 through 2000.
January 18, 2003 at 1:16 pm
Something else to keep in mind is that not all errors are errors - some are business logic related. If you know an update should only affect one row and it updates zero, sql doesn't raise an error, but you might. Im a strong advocate of using raiserror rather than return values. Not that return values don't have their uses, but as a developer its easier to work with procs that raise errors, code jumps into the local error handler. Think of a proc as being just like any other component you drop into an app. Most 3rd party components raise errors rather than forcing you to check return values on each call.
Andy
January 18, 2003 at 6:39 pm
I know some like the idea of returning a value via an OUTPUT parameter, but I'm rather on the same side as Andy. If you're using RAISERROR and trapping in the application code, you only have to worry about one method to check errors. If you have OUTPUT parameters then you have to check the OUTPUT parameter and an error being returned.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply