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:

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.