Exception management in stored procedures?

Question

I inherited an application with a lot of stored procedures, and many of them have exception handling code that inserts a row in an error table and sends a DBMail. We have ELMAH on the ASP.NET side, so I'm wondering if exception management in the stored procs is necessary. But before I rip it out, I want to ensure that I'm not making a grave mistake because of ignorance about a best practice.

Only one application uses the stored procedures.

When would one prefer using exception management in a SQL Server 2005 stored procedure over handling the exception on the ASP.NET side?

Answer 1

If there are other applications utilizing these stored procedures then it might make sense to retain the error handling in the stored procedures. In your edit you indicate that this is not the case so removing the exception handling is probably not a bad idea.

In the MSDN article Exception Handling it is outlined when to catch exceptions and when to let them bubble up the stack. It can be argued that it makes sense to handle and log database exceptions that are recoverable from in the stored procedure.

Answer 2

There is a principle sometimes referred to as "First Failure Data Capture" - ie. it's the responsibility of the first "chunk of code" that indentifies an error to immediately capture it for future diagnosis. In multi-tier architectures this leads to some interesting questions about who "first" actually is.

I believe that it's quite reasonable for the stored procedure to log something to a db (sending an email sounds somewhet overkill for all but the most critical of errors but that's another issue). It cannot assume taht higher layers will be well behaved, you may only have one client now, but you can't predict the future.

The stored procedure can still throw an exception as well as logging. And sometimes in difficult situations being able to correlate errors in the different layers is actually very handy.

I would take a lot of persuading to remove that error logging.

Answer 3

I believe that logging to a table only works for simpler systems where everything is done within a single stored procedure call.

Once the system is complex enough that you implement transactions across database calls, logging to the database within the stored procedure becomes much more of a problem.

The rollbacks undo the logging to the table.

Logic that allows rollbacks and logging, in my opinion, creates too much potential for defects.