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?

Cassondracassoulet

Just tested and RAISERROR will in fact terminate execution if the severity is set to 17 or 18, instead of 16. – Stanfordstang

Just tested SQL Server 2012 and RAISERROR will in fact not terminate execution if the severity is set to 17 or 18, instead of 16. – Unbelt

The reason is clearly explained by Erland Sommarskog (SQL Server MVP since 2001) in part 2 of his excellent series Error and Transaction Handling in SQL Server: "Every once in a while, I get the feeling that SQL Server is intentionally designed to be as confusing as possible. When they plan for a new release they ask each other what can we do this time to confuse the users? Sometimes they run a little out of ideas, but then someone says Let's do something with error handling!" – Sanyu

@IanBoyd, indeed, even after setting severity to 17 or 18 or 19 execution is not stopped. What's more interesting, if you look in Messages tab you will see no (X rows affected) or PRINT messages, which I would say is a complete lie! – Gerhardine

Answer 1

This is By Design^TM, as you can see on Connect by the SQL Server team's response to a similar question:

Thank you for your feedback. By design, the XACT_ABORT set option does not impact the behavior of the RAISERROR statement. We will consider your feedback to modify this behavior for a future release of SQL Server.

Yes, this is a bit of an issue for some who hoped RAISERROR with a high severity (like 16) would be the same as an 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.

Hartsfield

Thanks Philip. The link you referenced seems to be unavailable. – Cassondracassoulet

Link is working fine, if ever you need to search for it, title "Have RAISERROR work with XACT_ABORT", author "jorundur", ID: 275308 – Parity

Link is dead, with no archive.org cached copy. It's been lost to the sands of time forever. – Unbelt

This answer is a good backup - with a link to the docs where this behaviour is made clear. – Desantis

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

Cheer

Answer 3

Use RETURN immediately after RAISERROR() and it'll not execute the procedure further.

Orcus

You may want to call rollback transaction before calling return. – Dripstone

Probably you may need to do something in your catch block – Loredo

Answer 4

As pointed out on the docs for SET XACT_ABORT, the THROW statement should be used instead of RAISERROR.

The two behave slightly differently. But when XACT_ABORT is set to ON, then you should always use the THROW command.

Barnacle

If you don't have 2k12 (or above when it comes out), then there is no THROW statement to be had. – Symbiosis

Answer 5

microsoft suggests using throw instead of raiserror. Use XACT_State to determine commit or rollback for the try catch block

set XACT_ABORT ON;

BEGIN TRY
     BEGIN TRAN;
    
     insert into customers values('Mark','Davis','[email protected]', '55909090');
    insert into customer values('Zack','Roberts','[email protected]','555919191');
    COMMIT TRAN;
  END TRY

BEGIN CATCH
    IF XACT_STATE()=-1
        ROLLBACK TRAN;
    IF XACT_STATE()=1
       COMMIT TRAN;
    SELECT ERROR_MESSAGE() AS error_message
END CATCH

Premonish