Why does Sql Server keep executing after raiserror when xact_abort is on?

Question

I just got surprised by something in TSQL. I thought that if xact_abort was on, calling something like

raiserror('Something bad happened', 16, 1);

would stop execution of the stored procedure (or any batch).

But my ADO.NET error message just proved the opposite. I got both the raiserror error message in the exception message, plus the next thing that broke after that.

This is my workaround (which is my habit anyway), but it doesn't seem like it should be necessary:

if @somethingBadHappened
    begin;
        raiserror('Something bad happened', 16, 1);
        return;
    end;

The docs say this:

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

Does that mean I must be using an explicit transaction?

Answer 1

This is By Design(tm), as you can see on Connect by the SQL Server team's response to a similar question.

Yes, this is a bit of an issue for some who hoped raiseerror with a high severity (like 16) would be the same as a sql execution error - it's not.

Your workaround is just about what you need to do, and using an explicit transaction doesn't have any effect on the behavior you want to change.

Answer 2

If you use a try/catch block a raiserror error number with severity 11-19 will cause execution to jump to the catch block.

Any severity above 16 is a system error. To demonstrate the following code sets up a try/catch block and executes a stored procedure that we assume will fail:

assume we have a table [dbo].[Errors] to hold errors assume we have a stored procedure [dbo].[AssumeThisFails] which will fail when we execute it

-- first lets build a temporary table to hold errors
if (object_id('tempdb..#RAISERRORS') is null)
 create table #RAISERRORS (ErrorNumber int, ErrorMessage varchar(400), ErrorSeverity int, ErrorState int, ErrorLine int, ErrorProcedure varchar(128));

-- this will determine if the transaction level of the query to programatically determine if we need to begin a new transaction or create a save point to rollback to
declare @tc as int;
set @tc = @@trancount;
if (@tc = 0)
 begin transaction;
else
 save transaction myTransaction;

-- the code in the try block will be executed
begin try
 declare @return_value = '0';
 set @return_value = '0';
 declare
  @ErrorNumber as int,
  @ErrorMessage as varchar(400),
  @ErrorSeverity as int,
  @ErrorState as int,
  @ErrorLine as int,
  @ErrorProcedure as varchar(128);


 -- assume that this procedure fails...
 exec @return_value = [dbo].[AssumeThisFails]
 if (@return_value <> 0)
  raiserror('This is my error message', 17, 1);

 -- the error severity of 17 will be considered a system error execution of this query will skip the following statements and resume at the begin catch block
 if (@tc = 0)
  commit transaction;
 return(0);
end try


-- the code in the catch block will be executed on raiserror("message", 17, 1)
begin catch
  select
   @ErrorNumber = ERROR_NUMBER(),
   @ErrorMessage = ERROR_MESSAGE(),
   @ErrorSeverity = ERROR_SEVERITY(),
   @ErrorState = ERROR_STATE(),
   @ErrorLine = ERROR_LINE(),
   @ErrorProcedure = ERROR_PROCEDURE();

  insert #RAISERRORS (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
   values (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure);

  -- if i started the transaction
  if (@tc = 0)
  begin
   if (XACT_STATE() <> 0)
   begin
     select * from #RAISERRORS;
    rollback transaction;
    insert into [dbo].[Errors] (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
     select * from #RAISERRORS;
    insert [dbo].[Errors] (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
     values (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure);
    return(1);
   end
  end
  -- if i didn't start the transaction
  if (XACT_STATE() = 1)
  begin
   rollback transaction myTransaction;
   if (object_id('tempdb..#RAISERRORS') is not null)
    insert #RAISERRORS (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
     values (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure);
   else
    raiserror(@ErrorMessage, @ErrorSeverity, @ErrorState);
   return(2); 
  end
  else if (XACT_STATE() = -1)
  begin
   rollback transaction;
   if (object_id('tempdb..#RAISERRORS') is not null)
    insert #RAISERRORS (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
     values (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure);
   else
    raiserror(@ErrorMessage, @ErrorSeverity, @ErrorState);
   return(3);
  end
 end catch
end

Answer 3

Just a small addition - I ran into this Adam Machanic demo scripts recently. He goes through the errors runtime/batch abort, etc

http://sqlblog.com/blogs/adam_machanic/archive/2008/06/05/demos-from-my-teched-session-dat302-best-practices-for-exception-handling-and-defensive-programming-in-microsoft-sql-server-2005-and-2008.aspx