error handling mechanism in SQL 2005

BEGIN TRAN

  INSERT Northwind.dbo.Region(RegionId, RegionDescription) VALUES (5,'CENTRAL')

  PRINT 'Added Central Region'

  INSERT Northwind.dbo.Region(RegionId, RegionDescription) VALUES (5,'ANOTHER REGION')

  PRINT 'Added Another Region'

  INSERT Northwind.dbo.Region(RegionId, RegionDescription) VALUES (6,NULL)

  PRINT 'Added NULL Region'

  COMMIT TRAN

END TRY

BEGIN CATCH

IF ERROR_NUMBER() = 2627

BEGIN

    PRINT 'Statement violates primary key constraints.'

    IF (XACT_STATE()) <> 0 -- We are in a transaction and want to roll it back

        ROLLBACK TRAN

END

ELSE IF ERROR_NUMBER() = 515

BEGIN

    PRINT 'Statement attempted to insert a NULL where a NULL is not allowed.'

    IF (XACT_STATE()) = -1 -- Can only rollback

        ROLLBACK TRAN

    ELSE IF (XACT_STATE()) = 1 -- We can do whatever we want, commit or roll back

        COMMIT TRAN

END

PRINT 'Error Number:   ' + CAST(ERROR_NUMBER() AS VARCHAR(10))

PRINT 'Error Message:  ' + ERROR_MESSAGE()

PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10))

PRINT 'Error State   : ' + CAST(ERROR_STATE() AS VARCHAR(10))

END CATCH

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/anthonybloesch/attachment/9469577.ashx.

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):

if parameter 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):

if parameter 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;