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?

  • 2
    Try using the newer TRY CATCH block in SQL Server 2005. sommarskog.se/error_handling_2005.html Commented Apr 20, 2011 at 21:55
  • Hi @Kacalapy ~ I'ld like to recommend in the future to ask each question in its own right and that way we can have specific answers focused on one question at a time. I encourage you to do that with this question.
    – jcolebrand
    Commented Apr 21, 2011 at 14:11

3 Answers 3


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.

  • Great advice and a very good book!
    – Marian
    Commented Apr 21, 2011 at 7:25
  • Red Gate offers several extremely helpful free E-books, and this one is certainly one of the better ones. Great suggestion.
    – Matt M
    Commented Apr 22, 2011 at 12:42
  • Not all of their books do this, but the free version of Kuznetsov's "Defensive..." book does not contain the last 2 chapters on Transaction Isolation Levels and Developing Modifications that survive concurrency. For me. the content in there was worth the purchase. Commented Apr 23, 2011 at 0:59

This is our template (error logging removed)


  • 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



DECLARE @starttrancount int

    SELECT @starttrancount = @@TRANCOUNT

    IF @starttrancount = 0

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

    IF @starttrancount = 0 
    IF XACT_STATE() <> 0 AND @starttrancount = 0 
    RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]

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'
  insert into MyTable
  select Values
    from MyOtherTable

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


select @output = upper(@TableAction) + 
       ' ERROR - An error occurred while ' + 
       case (@TableAction)
         when 'update' then 'updating'
         when 'delete' then 'deleting'
         else @TableAction + 'ing'
       end + 
       ' records ' + 
         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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.