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

Statement Termination Screenshot

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.

Statement Termination Screenshot 2a

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.

Statement Termination Screenshot 2b

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.

Scope Abortion Screenshot

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.

Batch Termination Screenshot

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.

XACT_ABORT ON Screenshot

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.

XACT_ABORT OFF Screenshot


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 (http://blog.sqlauthority.com)

About these ads

9 thoughts on “SQL SERVER – Introduction to SQL Error Actions – A Primer

  1. BEGIN TRY

    Statements 1
    .
    .
    .
    END TRY
    BEGIN CATCH
    IF @@TRANCOUNT > 0
    ROLLBACK

    END CATCH

    In try catch block, the next statement will be executed after error occurred.

    Like

  2. Dear Dave, you write: SQL Server will now choose which errors are drastic enough to fail an entire stored procedure (or batch) – under XACT_ABORT OFF.

    Are there any rules the decisions are made?

    Like

  3. Pingback: SQL SERVER – Quiz and Video – Introduction to SQL Error Actions « SQL Server Journey with SQL Authority

  4. Pingback: SQL SERVER – SQL in Sixty Seconds – 5 Videos from Joes 2 Pros Series – SQL Exam Prep Series 70-433 « SQL Server Journey with SQL Authority

  5. Pingback: SQL SERVER – 5 Videos from Joes 2 Pros Series Exam Prep Series 70-433 – SQL in Sixty Seconds « SQL Server Journey with SQL Authority

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #026 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s