SQL SERVER – Introduction to SQL Error Actions – A Primer

This blog post is inspired from SQL Programming Joes 2 Pros: Programming and Development for Microsoft SQL Server 2008 – SQL Exam Prep Series 70-433 – Volume 4.

[Amazon] | [Flipkart] | [Kindle] | [IndiaPlaza]

Introduction to SQL Error Actions

Most people believe that when SQL Server encounters an error severity level 11 or higher the remaining SQL statements will not get executed. In addition, people also believe that if any error severity level of 11 or higher is hit inside an explicit transaction, then the whole statement will fail as a unit. While both of these beliefs are true 99% of the time, they are not true in all cases. It is these outlying cases that frequently cause unexpected results in your SQL code.

To understand how to achieve consistent results you need to know the four ways SQL Error Actions can react to error severity levels 11-16:

1. Statement Termination – The statement with the procedure fails but the code keeps on running to the next statement. Transactions are not affected.
2. Scope Abortion – The current procedure, function or batch is aborted and the next calling scope keeps running. That is, if Stored Procedure A calls B and C, and B fails, then nothing in B runs but A continues to call C. @@Error is set but the procedure does not have a return value.
3. Batch Termination – The entire client call is terminated.
4. XACT_ABORT – (ON = The entire client call is terminated.) or (OFF = SQL Server will choose how to handle all errors.)

There is an additional SQL Error Action for error severity levels 20-25 that will not be covered in this exercise:

• Connection Termination – The client is disconnected and any open transaction is rolled back. This occurs when something really bad happens like an overflow or protocol error in the client library.

In the following examples you will be following along with the effects SQL Error Actions have on three different stored procedures, usp_A, usp_B and usp_C.

Statement Termination

If an error is encountered in Statement 2 of usp_B, SQL Server might decide to proceed to Statement 3. This is the least disruptive reaction possible to an error; a single statement fails to run but all other statements continue operating as expected.

In a Statement Termination there is no disruption to the calling code, and usp_A continues by running the rest of its code, including all the statements in usp_C.

Now let’s attempt to generate an error by sending in a NULL value for ‘Amount’ in the figure below. We expect this will produce an error, because the ‘Amount’ field cannot be NULL. When reading the message shown after executing this stored procedure you see the expected error stating that a NULL cannot be inserted into the ‘Amount’ field (error severity level 16). Now look closely at the message pane and notice that the second statement ran and updated the Employee table.

Clearly a NULL inserted into a non-nullable field has caused a Statement Termination action by SQL Server. However; since a null violation is considered less drastic, it generated only a single statement termination. This prevented the first statement from running, yet allowed the second statement to run unaffected by the previous error.

The screenshot below shows that the first statement threw an error and the stored procedure continued execution of the next statement.

Scope Abortion

In Scope Abortion, the failed statement causes the function or stored procedure to fail. While usp_B halts after one failed statement, the execution from usp_A will continue to run by calling on usp_C.

Batch Termination

With Batch Termination, a failure of statement 2 inside of usp_B will cause both usp_B to fail and usp_A to return a failure to the calling code, thus the call to usp_C never takes place.

XACT_ABORT

Recall the previous Statement Termination example where the Employee timestamp field of ‘LatestGrantActivity’ was updated, despite the corresponding ‘Grant’ record not successfully updating. You might decide that in all cases of an error within your stored procedure it would be better to abort the entire batch. Otherwise, the timestamp field‘LatestGrantActivity’ will be out of sync with the updates which were actually made to the ‘Grant’ record.

In this situation, you can use the SQL Server command XACT_ABORT (short for transact abort). This means all errors with an error severity level 11-16 will result in a Batch Termination. After setting XACT_ABORT ON, you can see that the second update did not run after the first error was encountered.

By setting XACT_ABORT OFF (the default configuration), SQL Server will now choose which errors are drastic enough to fail an entire stored procedure (or batch). In other words, SQL Server will pick the error action based on the error which was raised. The screenshot below shows the effects of this setting by making the same call to the‘AddGrantAmount’ stored procedure used in the previous example.

This blog post is inspired from SQL Programming Joes 2 Pros: Programming and Development for Microsoft SQL Server 2008 – SQL Exam Prep Series 70-433 – Volume 4.

[Amazon] | [Flipkart] | [Kindle] | [IndiaPlaza]

Reference: Pinal Dave (https://blog.sqlauthority.com)