SQL Server 2008 error handling best practice


Error handling in SQL Server 2008 needs careful implementation. The Microsoft “Oslo” Repository’s API has the further problem that we cannot mandate the error handling logic in our callers. Thus a stored procedure call could be in a transaction or not and in a try-catch block or not. Below is the pattern we have chosen based on experimentation and best practice guidance from the SQL Server team and other SQL Server experts. A test script for all the interesting cases can be found at http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-components-postattachments/00-09-46-95-77/Error-Handling.sql.

For a good overview of SQL Server error handling see http://www.sommarskog.se/error-handling-I.html and http://www.sommarskog.se/error-handling-II.html. In our design, we had the following main issues and mitigations:

·         A transaction rollback will rollback to the outermost transaction but if there is an outer transaction we would like to only rollback the changes to the module’s inner transaction. The solution is to use SQL Server save points if there is an outer transaction.

·         A sufficiently severe raiserror will terminate a module if there is an outer try-catch block but if there is none then execution will continue in the module. The solution is to always explicitly return after raising an error.

·         Some developers like to use stored procedure return values to encode error states. The solution is to return appropriate error codes.

·         SQL Server will raise warnings if the transaction depth entering and leaving a transaction do not match. The solution is to be careful.

·         Triggers have an implicit transaction. The solution is to use a simplified pattern for triggers where a transaction is never started.

·         Save points need unique names if modules can nest otherwise you can rollback to the wrong save point. The solution is to use a GUID to name the save points.

Here is the pattern for stored procedures (eliding our special error reporting routines):

ifparameter error
begin
  raiserror(N’…’, 16, 0);

  return 1;
end
else
begin
  begin try
    declare @hasOuterTransaction bit = case when @@trancount > 0 then 1 else 0 end;
    declare @rollbackPoint nchar(32) = replace(convert(nchar(36), newid()), N’-‘, N”);
   
    if @hasOuterTransaction = 1
    begin      
      save transaction @rollbackPoint;
    end
    else
    begin
      begin transaction @rollbackPoint;
    end;

    Do work;

    if @hasOuterTransaction = 0
    begin
      commit transaction;
    end;
  end try
  begin catch
    if xact_state() = 1
    begin
      rollback transaction @rollbackPoint;
    end;

    execute Standard module error handler;

    return error_number();
  end catch;
end;

Here is the pattern for triggers (eliding our special error reporting routines):

ifparameter error
begin

  rollback transaction;


  raiserror(N’…’, 16, 0);

 

  return;
end
else
begin
  begin try

   
Do work;

  end try
  begin catch
    rollback transaction;

    execute
Standard module error handler;

    return;
  end catch;
end;

 

 

Error Handling.sql

Comments (9)

  1. Anonymous says:

    Rolling back the transaction in the trigger doesn’t work and always generates the so called informational error message about an aborted transaction. The only real work around that I have found was to remove the try…catch from the trigger and only use it in procedures. For some reason the it doesn’t work well with triggers. Also, rollback in a trigger will almost always generate an aborted ransaction statement from sql. I have been messing around with this for days trying to make it work.

  2. Anonymous says:

    For stored procedues, in the catch block, it is not currently handling the case where there is no outer transaction and the transaction state is uncommitable.

    Should the catch block be as below?

    — an error occurred, we must rollback only the work done in this sproc

    IF @hasOuterTransaction = 0

    BEGIN

    -- we started the transaction, so rollback the complete transaction
    
    ROLLBACK TRANSACTION
    

    END

    ELSE

    BEGIN

    -- there is an outer transaction, do not rollback changes before this sproc was called
    
    IF XACT_STATE() <> -1
    
    BEGIN
    
        -- the transaction is still valid, just rollback to the save point
    
        ROLLBACK TRANSACTION @rollbackPoint
    
    END
    
    -- else that means the transaction is uncommitable, just let the caller rollback the transaction
    

    END

    — error handling

  3. Anonymous says:

    Great stuff, Anthony.  I really like your recommendations.

    -Kevin

    Twitter @kekline

  4. Anonymous says:

    No way that the save transaction  @rollbackPoint; in the template you submit is to be used: case is when a calling procedure remotely calls another one. You’ll catch an error as save transaction is not supported in remote calls (Too bad!!!!).

    BOL: [SAVE TRANSACTION is not supported in distributed transactions started either explicitly with BEGIN DISTRIBUTED TRANSACTION or escalated from a local transaction.]

    I had the issue and had to completely review all my template.

    Hope this will help

  5. Anonymous says:

    what this error means:

    Incorrect syntax near 'AF14C8CF'the floating point value '28E218132931' is out of the range of computer representation (8 bytes). Unclosed quotation mark after the character string 'order by datname'

  6. Anonymous says:

    Has anyone addressed the issue with distributed transactions/remotely executed calls (noted by "PR")?

    I have few instances where this applies, however it is prudent, as we are discussing 'pattern' and not just implementation.

    Thanks!

  7. Anonymous says:

    I like the this error handling pattern but it has issue to deal with doomed transaction.

  8. Anonymous says:

    hi is there a way to do a try catch block that does all or none procedures something like this.

    try

    proc1

    proc2

    proc3

    catch

    rollback

    endtry

    i mean to do all or do none?

    thank you