Error handling is a part of any well-written code and should be part of any stable database. You can't always account for why things go wrong, but you can provide an exit strategy in your code that returns your database to a consistent state, bails out on the routine or provides the necessary clues so that you can track down what went wrong and why.
Stored procedures are a good place to use your error handling code because these routines are the ones that are the most stable and run the most often in a database. Stored procedures are often used to enforce the business rules of your DBMS, so many stored procedures are written in a modular way building upon other routines in your system.
Assuming you have developed a set of error handling routines, calling those routines from a standard procedure should depend upon evaluating a condition that exists after the stored procedure has run a significant piece of its code. Typically programmers set up their code so that a variable is populated with a return code, most often an integer value. For example, a "0" might indicate successful completion of the procedure, while "55" or "505" would indicate two different problems that occurred. Evaluation of the test condition necessary to exit the stored procedure would test this variable and exit for 0 or run the error routine for any other number.
Another aspect of error handling in stored procedures is to make sure that you are implementing your transactional recovery routines properly. Make sure that ROLLBACK and @@TRANCOUNT actually work.
Also be certain to pay particular attention to things like T-SQL BULK INSERT operations that require the use of the DTC. You can tell whether a BULK INSERT worked by examining the @@ERROR global variable, provided you check that variable directly after the statement of concern. Your code needs to reset the @@ERROR to 0 once the code runs. You can use the @@ERROR variable to initiate error handling for errors that typically don't stop a stored procedure (or a batch operation for that matter) from running. Typical examples where this might come in handy are when an aggregate function doesn't run properly or a constraint has been violated. Use the @ErrorCollector variable if you need to hold the error value for a return code. The @ErrorCollector value will examine transactions for their data integrity and when a stored procedure doesn't run properly it populates that variable with an error code.
Barrie Sosinsky is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.
