When working with SQL Server, it is important to understand how error actions can affect the execution of your code. Most people believe that when SQL Server encounters an error severity level 11 or higher, the remaining SQL statements will not get executed. While this is true in most cases, there are exceptions that can lead to unexpected results.
There are four ways SQL Error Actions can react to error severity levels 11-16:
- Statement Termination: The statement with the error fails, but the code continues to run to the next statement. Transactions are not affected.
- Scope Abortion: The current procedure, function, or batch is aborted, but the calling scope continues to run. If a stored procedure A calls B and C, and B fails, then B does not run, but A continues to call C. The procedure does not have a return value.
- Batch Termination: The entire client call is terminated.
- XACT_ABORT: This setting determines how SQL Server handles errors. When set to ON, the entire client call is terminated. When set to OFF, SQL Server will choose how to handle errors.
It is important to note that there is an additional SQL Error Action for error severity levels 20-25, which is connection termination. This occurs when something catastrophic happens, such as an overflow or protocol error in the client library.
Let’s take a look at some examples to better understand these error actions.
Statement Termination
In this scenario, if an error is encountered in a statement, SQL Server might decide to proceed to the next statement. This is the least disruptive reaction to an error, as only the failing statement is affected. The calling code continues to run as expected.
Scope Abortion
In scope abortion, if a statement fails, the function or stored procedure also fails. However, the calling scope continues to run. This means that if a stored procedure A calls B and B fails, B does not run, but A continues to call other procedures or statements.
Batch Termination
If a statement fails within a batch, both the current batch and any calling code will fail. This means that if a stored procedure A calls B and B fails, A will return a failure to the calling code, and any subsequent calls, such as calling procedure C, will not take place.
XACT_ABORT
XACT_ABORT is a setting that determines how SQL Server handles errors. When set to ON, all errors with severity levels 11-16 will result in a batch termination. This means that if an error occurs, the entire batch will be aborted. When set to OFF (the default configuration), SQL Server will choose which errors are drastic enough to fail an entire stored procedure or batch.
Understanding these error actions and how they can affect your code is crucial for writing robust and reliable SQL Server applications. By knowing how SQL Server handles errors, you can ensure consistent and expected results in your code.
For more in-depth information on SQL Server error actions, I recommend reading the book “SQL Programming Joes 2 Pros: Programming and Development for Microsoft SQL Server 2008 – SQL Exam Prep Series 70-433 – Volume 4”.