Why does Sql Server continue to run after raiserror when xact_abort is enabled?

Question

I was just surprised at something in TSQL. I thought that if xact_abort is enabled, something like

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

terminates the execution of the stored procedure (or any batch).

But my ADO.NET error message just proved the opposite. I received a raiserror error message in the exception message, as well as the following, which then broke.

This is my workaround (this is my habit anyway), but it doesn't seem like it's necessary:

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

The docs say the following:

When SET XACT_ABORT is turned on, if the Transact-SQL statement raises a run-time error, the whole transaction is completed and rolled back.

Does this mean that I should use an explicit transaction?

Answer 1

This is a ^TM design as you can see on Connect with the response of the SQL Server team to a similar question:

Thank you for your feedback. By design, the XACT_ABORT parameter does not affect the behavior of the RAISERROR statement. We will review your feedback to change this behavior for a future version of SQL Server.

Yes, this is a bit of a problem for those who hoped that a highly serious RAISERROR (e.g. 16 ) would be the same as an SQL runtime error - it is not.

Your workaround is what you need to do, and using an explicit transaction does not affect the behavior you want to change.

Answer 2

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

Any severity above 16 is a system error. To demonstrate the following codes, create a try / catch block and execute a stored procedure that we assume will not execute:

Suppose we have a table [dbo]. [Errors] to store errors, suppose we have a stored procedure [dbo]. [AssumeThisFails], which will fail during its execution

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

Use RETURN immediately after RAISERROR() , and it will not perform further procedure.

Answer 4

As indicated on MSDN , the RAISERROR should be used instead of RAISERROR .

Both behave somewhat differently . But when XACT_ABORT set to ON, you should always use the THROW command.