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

What's going on?

The command XACT_ABORT ON is made to abort transactions when run-time errors show up. But, RAISERROR? It’s not a fan of automatically rolling back a transaction unless it's presented with a severity level of over 18.

When hit with a RAISERROR, SQL Server, relentless as ever, charges forth after the error unless we stop it. How do we stop it? Sure, we can pull the plug on execution using a RETURN statement or by setting off a severity alarm of over 20.

Delving into the "RETURN" necessity

Using a RAISERROR with a severity under 20 is like pressing the panic button but not waiting for rescue; it does not withdraw from the execution path. If you want to stop proceedings, use a RETURN following the RAISERROR. This mimics XACT_ABORT’s hard stop:

RAISERROR('This error won’t play nice and stop things.', 16, 1); 
RETURN;  -- Every stop sign you blew past in SQL code


Best Practice: Make your intentions clear, always club your RAISERROR with a RETURN.

The intensity of XACT_ABORT and severity levels

Even if you’re prepared with XACT_ABORT ON, severity levels under 20 won’t make SQL Server cease running subsequent statements. If you want to force the issue, you will have to opt for an error level of 20 or above. This will compulsorily stop execution, irrespective of XACT_ABORT.

RAISERROR('Severity 20 unleashed; SQL Server stops in its tracks!', 20, 1); 
-- Nothing runs after this line, not even Usain Bolt


Preempting errors prior to RAISERROR

Be proactive! Avoid hiccups in execution by being on the lookout for errors before the RAISERROR comes into play. Evaluate conditions and handle them accordingly:

IF @ErrorFound = 1
BEGIN
    RAISERROR('Oh, an error popped up!', 16, 1);
    RETURN;
END


Tip: Be a step ahead with preemptive checks: your transaction flow will thank you for its integrity.

Mitigations and considerations

When RAISERROR throws a tantrum and you need to put an end to the execution, here are some things you should keep in mind:

• Severity matters: Errors below severity level 20 will allow execution to continue. Drum up the severity beyond this, and the engine will do the stopping.
• Control flow with RETURN: A RETURN statement right after RAISERROR will assure the end of execution. No taking chances!
• TRY...CATCH could be your friend: A TRY…CATCH can give you control over error handling, allowing you to clean up, or even log before halting.

Real-world instances

Here’s when practicality steps in:

• Conditional checks: Ascertain if conditions are ripe before escalating with RAISERROR.
• Step into the testing waters: Try out different severity levels and see how your code is affected.
• Logging and Rollback: Use TRY...CATCH to log those pesky errors and perform rollbacks when needed.

Remember: Understand your arsenal and how to wield it for resilient, dynamic applications.