How Exceptions Work in SQL Server

This is Part 2 of a series of blog posts by Data Education founder Adam Machanic on errors and exceptions in Microsoft SQL Server. The posts will cover everything from the TRY/CATCH syntax to the delicate relationship between transactions and exceptions. In Part 1, Adam gave a basic explanation of the difference between errors and exceptions. In this second post, he talks types of exceptions.

The first step in understanding how to handle errors and exceptions in SQL Server is to take a look at how the server itself deals with error conditions. Unlike many other programming languages, SQL Server has an exception model that involves different behaviors for different types of exceptions. This can cause unexpected behavior when error conditions do occur, so careful programming is essential when dealing with T-SQL exceptions.

To begin with, think about connecting to a SQL Server and issuing some T-SQL. First, you must establish a connection to the server by issuing login credentials. The connection also determines what database will be used as the default for scope resolution (i.e., finding objects—more on this in a bit). Once connected, you can issue a batch of T-SQL. A batch consists of one or more T-SQL statements, which will be compiled together to form an execution plan.

The behavior of the exceptions thrown by SQL Server mostly follows this same pattern. Depending on the exception, a statement, a batch, or an entire connection may be aborted. Let’s take a look at some examples to clarify what this means.

Statement-Level Exceptions

A statement-level exception aborts only the current statement that is running within a batch of T-SQL, allowing the subsequent statements within the batch to run. To see this behavior, use SQL Server Management Studio to execute a batch that includes an exception, followed by a PRINT statement. For instance:

SELECT POWER(2, 32)
PRINT 'This will print!'
GO

Running this batch results in the following output:

Msg 232, Level 16, State 3, Line 1
Arithmetic overflow error for type int, value = 4294967296.000000.
This will print!

When this batch was run, the POWER(2, 32) caused an integer overflow, which threw the exception. However, only the SELECT statement was aborted. The rest of the batch continued to run, which in this case means that the PRINT statement printed its message.

Batch-Level Exceptions

Unlike a statement-level exception, a batch-level exception does not allow the rest of the batch to continue running. The statement that throws the exception will be aborted, and any remaining statements in the batch will not be run. An example of a batch-aborting exception is an invalid conversion, such as the following:

SELECT CONVERT(INT, 'abc')
PRINT 'This will NOT print!'
GO

The output of this batch is as follows:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'abc' to data type int.

In this case, the conversion exception occurred in the SELECT statement, which aborted the entire batch. The PRINT statement was not allowed to run.

Batch-level exceptions might be easily confused with connection-level exceptions (which drop the connection to the server), but after a batch-level exception, the connection is still free to send other batches. For instance:

SELECT CONVERT(INT, 'abc')
GO
PRINT 'This will print!'
GO

In this case there are two batches sent to SQL Server, separated by the batch separator, GO. The first batch throws a conversion exception, but the second batch is still run. This results in the following output:

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value 'abc' to data type int.
This will print!

Batch-level exceptions do not affect only the scope in which the exception occurs. The exception will bubble up to the next level of execution, aborting every call in the stack. This can be illustrated by creating the following stored procedure:

CREATE PROCEDURE ConversionException
AS
BEGIN
    SELECT CONVERT(INT, 'abc')
END
GO

Running this stored procedure with a PRINT shows that even though the exception occurred in an inner scope (within the stored procedure), the outer batch is still aborted:

EXEC ConversionException
PRINT 'This will NOT print!'
GO

The result of this batch is the same as if no stored procedure was used:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'abc' to data type int.

Parsing and Scope-Resolution Exceptions

Exceptions that occur both during parsing and during the scope-resolution phase of compilation appear at first to behave just like batch-level exceptions. However, they actually have a slightly different behavior. If the exception occurs in the same scope as the rest of the batch, these exceptions will behave just like a batch-level exception. If, on the other hand, an exception occurs in a lower level of scope, these exceptions will behave just like statement-level exceptions—at least, as far as the outer batch is concerned.

As an example, consider the following batch, which includes a malformed SELECT statement (this is a parse exception):

SELECTxzy FROM SomeTable
PRINT 'This will NOT print!'
GO

In this case, the PRINT statement is not run because the whole batch is discarded during the parse phase. The output is the following exception:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'FROM'.

To see the difference in behavior, the SELECT statement can be executed as dynamic SQL using the EXEC function. This causes the SELECT statement to execute in a different scope, showing the change in behavior from batch-like to statement-like. The following T-SQL can be run to observe the change:

EXEC('SELECTxzy FROM SomeTable')
PRINT 'This will print!'
GO

The PRINT statement is now executed, even though the exception occurred:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'FROM'.
This will print!

This type of exception also occurs during scope resolution. Essentially, SQL Server processes queries in two phases. The first phase parses and validates the query and ensures that the T-SQL is well formed. The second phase is the compilation phase, during which an execution plan is built and objects referenced in the query are resolved. If a query is submitted to SQL Server via ad hoc SQL from an application or dynamic SQL within a stored procedure, these two phases happen together. However, within the context of stored procedures, SQL Server exploits late binding. This means that the parse phase happens when the stored procedure is created, and the compile phase (and therefore scope resolution) occurs only when the stored procedure is executed.

To see what this means, create the following stored procedure (assuming that a table called SomeTable does not exist in the current database):

CREATE PROCEDURE NonExistantTable
AS
BEGIN
    SELECT xyz
    FROM SomeTable
END
GO

Although SomeTable does not exist, the stored procedure is created—the T-SQL parses without any errors. However, upon running the stored procedure, an exception is thrown:

Msg 208, Level 16, State 1, Procedure NonExistantTable, Line 4
Invalid object name 'SomeTable'.

Like the parse exception, scope-resolution exceptions behave similarly to batch-level exceptions within the same scope, and similarly to statement-level exceptions in the outer scope. Since the stored procedure creates a new scope, hitting this exception within the procedure aborts the rest of the procedure, but any T-SQL encountered in the same batch after execution of the procedure will still run. For instance:

EXEC NonExistantTable
PRINT 'This will print!'
GO

Connection and Server-Level Exceptions

The remaining types of exceptions that can be thrown by SQL Server are those that abort the entire connection and those that cause the server itself to crash. These types of exceptions are generally caused by internal SQL Server bugs and are, thankfully, quite rare. I cannot provide any simple examples of these types of exceptions, as they can require preconditions such as corrupted data or faulty hardware.

The XACT_ABORT Setting

Although users do not have much control over the behavior of exceptions thrown by SQL Server, there is one setting that can be modified on a per-connection basis. Turning on the XACT_ABORT setting makes all statement-level, parsing, and scope-resolution exceptions behave like batch-level exceptions. This means that control will always be immediately returned to the client any time an exception is thrown by SQL Server. (This assumes that the exception is not handled.)

To enable XACT_ABORT for a connection, the following T-SQL is used:

SET XACT_ABORT ON

This setting will remain enabled for the connection—even if it was set in a lower level of scope, such as in a stored procedure or dynamic SQL—until it is disabled using the following T-SQL:

SET XACT_ABORT OFF

To illustrate the effect of this setting on the behavior of exceptions, let’s review a couple of the exceptions already covered. Recall that the following integer overflow exception operates at the statement level:

SELECT POWER(2, 32)
PRINT 'This will print!'
GO

Enabling the XACT_ABORT setting before running this T-SQL changes the output, resulting in the PRINT statement not getting executed:

SET XACT_ABORT ON
SELECT POWER(2, 32)
PRINT 'This will NOT print!'
GO

The output from running this batch is as follows:

Msg 232, Level 16, State 3, Line 2
Arithmetic overflow error for type int, value = 4294967296.000000.

Another example is a parsing exception in a lower scope. Recall that by default, the following exception does not abort the outer batch, but only the EXEC function:

EXEC('SELECTxzy FROM SomeTable')
PRINT 'This will print!'
GO

Just like the overflow exception, with XACT_ABORT set, the outer batch will be aborted in addition to the EXEC function, resulting in the PRINT statement not being evaluated.

In addition to controlling exception behavior, XACT_ABORT also modifies how transactions behave when exceptions occur. I will blog more about this later in this series.

Stay tuned, and thanks for reading!

1 Comment

  1. Jan NovakJan NovakOct 10, 2011
    7:00 PM

    Hi Adam.
    Thank you for a nice post! I enjoyed it and I am looking forward to your next post.

    Just today we had a talk about errors with my collegue regarding the scenario bellow, when SQL Server checks differently the existence of a column and a table in a stored procedure.

    USE tempdb
    GO
    
    CREATE TABLE ExistingTable(
    	bar INT NOT NULL
    )
    GO
    
    --Sql Server does not check for existence of table:
    CREATE PROCEDURE Valid
    AS
    BEGIN
        SELECT NonExistingColumn
        FROM NonExistingTable
    END
    GO
    
    --Sql Server check for existence of column if table exists and throws an error:
    CREATE PROCEDURE Invalid
    AS
    BEGIN
        SELECT NonExistingColumn
        FROM ExistingTable
    END
    GO