When xact abort is enabled, why does Sql Server continue to execute after raiserror?

Problem

Something in TSQL recently astonished me. If xact abort was enabled, I reasoned that calling something like

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

would put a stop to the stored procedure’s execution (or any batch).

My ADO.NET error message, on the other hand, proved the exact opposite. In the exception message, I got both the raiserror error message and the next thing that broke after that.

This is my workaround (which is also my habit), although it does not appear to be necessary:

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

This is what the doctors say:

Is that to say I have to use an explicit transaction?

Asked by Eric Z Beard

Solution #1

This is By DesignTM, as the SQL Server team’s response to a similar topic on Connect demonstrates:

Yes, this is a problem for those who thought RAISERROR with a high severity (like 16) meant a SQL execution fault – it doesn’t.

Your workaround is limited to what you need to achieve, and utilizing an explicit transaction has no effect on the desired behavior.

Answered by Philip Rieck

Solution #2

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

Any severity greater than 16 indicates a system issue. To show how, the following code creates a try/catch block and runs a stored function that we expect to fail:

Let’s pretend we have a [dbo] table. [Errors] to keep track of errors Let’s pretend we’ve got a stored procedure [dbo]. When we perform [AssumeThisFails], it will fail.

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

Answered by ninegrid

Solution #3

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

Answered by piyush

Solution #4

The THROW statement should be used instead of RAISERROR, as stated in the SET XACT ABORT documentation.

They act in slightly different ways. When XACT ABORT is set to ON, however, the THROW command should always be used.

Answered by Möoz

Solution #5

Instead of raiserror, Microsoft recommends throwing. For the try catch block, use XACT State to determine whether to commit or rollback.

set XACT_ABORT ON;

BEGIN TRY
     BEGIN TRAN;

     insert into customers values('Mark','Davis','markdavis@mail.com', '55909090');
    insert into customer values('Zack','Roberts','zackroberts@mail.com','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

Answered by Golden Lion

Post is based on https://stackoverflow.com/questions/76346/why-does-sql-server-keep-executing-after-raiserror-when-xact-abort-is-on