What is the best method to add error handling in SQL 2005 stored procs?

Question

What’s a good way to make stored procs robust enough that they can scale very well and also contain error handling?

Additionally, whats the best way to handle multiple error scenarios in a stored proc and have an intelligent feedback system that will return meaningful error information to the calling apps?

Answer 1

Alex Kuznetsov has a great chapter in his book Defensive Database Programming (Chapter 8) that covers T-SQL TRY...CATCH, T-SQL transactions & SET XACT_ABORT settings, and using client-side error handling. It will help you a lot in deciding which of the options makes the most sense for what you need to accomplish.

It is available for free at this site. I am in no way affiliated with the company, but I do own the hard copy version of that book.

There are a lot of little details on this subject that are explained very well by Alex.

Per Nick's request... (but not all of this is in the chapter)

In terms of scaling, you need to be brutally honest about which activities need to be in the db code and which should be in the app. Ever notice how fast-executing code tends to come back to designing for a single concern per method?

The easiest way to communicate would be custom error codes (> 50,000). It's also pretty fast. It does mean you'd have to keep the db code and app code in sync. With a custom error code, you can also return useful information in the error message string. Because you have an error code strictly for that situation, you can write a parser in the app code tailored to the error's data format.

Also, which error conditions need retry logic in the database? If you want to retry after X seconds, then you're better off handling that in the app code so the transaction doesn't block as much. If you are only re-submitting a DML operation right away, repeating it in the SP could be more efficient. Keep in mind, though, that you'll have to possibly duplicate code or add a layer of SPs to accomplish a retry.

Really, that's currently the biggest pain with TRY...CATCH logic in SQL Server at the moment. It can be done, but it's a bit of an oaf. Look for some improvements coming to this in SQL Server 2012, especially re-throwing system exceptions (preserving the original error number). Also, there's FORMATMESSAGE, which adds some flexibility in constructing error messages, especially for logging purposes.

Answer 2

This is our template (error logging removed)

Notes:

• Without XACT_ABORT, all TXN begin and commit/rollbacks must be paired
• A commit decrements @@TRANCOUNT
• A rollback returns @@TRANCOUNT to zero so you'd get error 266
• You can not ROLLBACK the current layer only (eg decrement @@TRANCOUNT on rollback)
• XACT_ABORT suppresses error 266
• Each stored proc must conform to the same template so each call is atomic
• The rollback check is actually redundant because of XACT_ABORT. However, it makes me feel better, looks odd without, and allows for situations where you don't want it on
• This allows for client side TXNs (like LINQ)
• Remus Rusanu has a similar shell that uses save points. I prefer an atomic DB call and don't use partial updates like their article

...so don't create more TXNs than you need

However,

CREATE PROCEDURE [Name]
AS
SET XACT_ABORT, NOCOUNT ON

DECLARE @starttrancount int

BEGIN TRY
    SELECT @starttrancount = @@TRANCOUNT

    IF @starttrancount = 0
        BEGIN TRANSACTION

       [...Perform work, call nested procedures...]

    IF @starttrancount = 0 
        COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 AND @starttrancount = 0 
        ROLLBACK TRANSACTION
    RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]
END CATCH
GO

Answer 3

I use Try/Catch, but I also gather as much information as possible and write that to an errorlog AFTER the rollback. In this example, "LogEvent" is a stored procedure that writes to an EventLog table, containing the details of what happened. GetErrorInfo() is a function call that returns the exact error message.

When an error occurs, the information is gathered, the procedure skips down to the error handling section and issues a rollback. The information is written to the log, then the procedure exits.

Considering the extra procedure/function calls involved, it seems a little over the top. HOWEVER this method is tremendously helpful when trying to debug the problem.

exec LogEvent @Process, @Database, 'Attempting to insert blah blah blah'
BEGIN TRY
  insert into MyTable
  select Values
    from MyOtherTable

  select @rowcount = @@ROWCOUNT
END TRY
-- ErrorHandling
BEGIN CATCH
  select @error          = ERROR_NUMBER(),
         @rowcount       = -1,
         @TableAction    = 'insert',
         @TableName      = @Database + '.MyTable',
         @AdditionalInfo = '(Attempting to insert blah blah blah) '  + dbo.GetErrorInfo()
   GOTO TableAccessError
END CATCH

.
.
.
.

TableAccessError:
IF (@@TRANCOUNT > 0) ROLLBACK
select @output = upper(@TableAction) + 
       ' ERROR - An error occurred while ' + 
       case (@TableAction)
         when 'update' then 'updating'
         when 'delete' then 'deleting'
         else @TableAction + 'ing'
       end + 
       ' records ' + 
       case(@TableAction) 
         when 'select' then 'from' 
         when 'update' then 'in' 
         when 'insert' then 'into'
         else 'from'   
         end  + 
         ' the ' + @TableName + ' table.'
select @output = @output + '  @@ERROR: '         + convert(varchar(8),@error) 
select @output = @output + '  @@ROWCOUNT: '      + convert(varchar(8),@rowcount) 

select @output = @output + isnull(@AdditionalInfo,'')
exec LogEvent @Process, @Database, @Output
RAISERROR(@output,16,1) with log
select @ReturnCode = -1
GOTO THE_EXIT