Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
Try using the newer TRY CATCH block in SQL Server 2005. sommarskog.se/error_handling_2005.html –  Sankar Reddy Apr 20 '11 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 Apr 21 '11 at 14:11
add comment

3 Answers

up vote 11 down vote accepted

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.

share|improve this answer
    
Great advice and a very good book! –  Marian Apr 21 '11 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 Apr 22 '11 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. –  Phil Helmer Apr 23 '11 at 0:59
add comment

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
share|improve this answer
    
what if @@TRANCOUNT is greater than 0? you dont do any work or have any feedback? –  kacalapy Apr 27 '11 at 20:23
    
@kacalapy: there is no such thing as a nested transaction so we don't start another scribd.com/doc/49579859/33/Nested-Transactions-Are-Real –  gbn Apr 28 '11 at 4:40
add comment

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


share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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