What Happens when an Error Occurs?

Many programming languages have a fairly consistent behaviour when there is a run-time error. Common is that the execution simply terminates in case of an error, unless you have set up an exception handler that takes care the error. In other languages, some error variable is set and you have to check this variable. T-SQL is confusing, because depending on what error that occurs and in which context it occurs, SQL Server can take no less than four different actions. I first give an overview of these alternatives, followed by a more detailed discussion of which errors that cause which actions. I then discuss two special cases: trigger context and user-defined functions.
The Possible Actions

These are the four main possible actions SQL Server can take:

Statement-termination. The current statement is aborted and rolled back. Execution continues on the next statement. Any open transaction is not rolled back. @@error is set to the number of the error. Since the statement is rolled back, this means that if you run an UPDATE statement that affects 1000 rows, and for one row a CHECK constraint is violated, none of the rows will be updated. But if the UPDATE statement was part of a longer transaction, the effect of the preceding INSERT, UPDATE or DELETE statements are not affected. You need to issue a ROLLBACK TRANSACTION yourself to undo them.

Scope-abortion. The current scope (stored procedure, user-defined function, or block of loose SQL statements, including dynamic SQL) is aborted, and execution continues on the next statement in the calling scope. That is, if stored procedure A calls B and B runs into a scope-aborting error, execution continues in A, just after the call to B. @@error is set, but the aborted procedure does not have a return value, but the variable to receive the return value is unaffected. As for statement-termination, any outstanding transaction is not affected, not even if it was started by the aborted procedure.

Batch-abortion. The execution of the entire batch – that is, the block of SQL statements that the client submitted to SQL Server – is aborted. Any open transaction is rolled back. @@error is still set, so if you would retrieve @@error first in the next batch, you would see a non-zero value. There is no way you can intercept batch-abortion in T-SQL code. (Almost. We will look a possibility using linked servers later on.)

Connection-termination. The client is disconnected and any open transaction is rolled back. In this case there is no @@error to access.

One can note from this, that there are two things that cannot happen:

    The transaction is rolled back, but execution of the current batch continues.
    The batch is aborted, but the transaction is not rolled back.

But I like to stress that this is based on my own observations. I have found no documentation that actually states that these two cases cannot occur under any circumstances.

The above caters for most of the error situations in SQL Server, but since a hallmark of the error handling in SQL Server is inconsistency, every now and then I discover some new odd situation. I am overlooking these cases here, not to burden the reader with too many nitty-gritty details.

There is however, one more situation you should be aware of and that is batch-cancellation. The client may at any time tell SQL Server to stop executing the batch, and SQL Server will comply more or less immediately. In this situation SQL Server will not roll back any open transaction. (In the general case that is. It seems that if the T-SQL execution is in a trigger, when the cancellation request comes, then there is a rollback.) However, if the current statement when the cancellation request comes in is an UPDATE, INSERT or DELETE statement, then SQL Server will roll back the updates from that particular statement. Batch-cancellation may occur because an explicit call to a cancellation method in the client code, but the most common reason is that a query timeout in the client library expires. ODBC, OLE DB, ADO and ADO.Net all have a default timeout of 30 seconds. (Which judging from the questions on the newsgroups, many programmers believe to come from SQL Server, but not so.)

Comments

Post a Comment

Popular posts from this blog

Google to FTS Syntax Cheat Sheet

Image
OPERATOR EXAMPLE DESCRIPTION   nut Searches for inflectional forms of the word nut crank arm crank AND arm Searches for documents containing inflectional forms of the words crank AND arm crank and ann. The keyword AND is optional. tire OR air Searches for documents containing inflectional forms of the words tire or air, “reflector bracket” Performs a phrase search for the phrase "reflector bracket". hardware -bracket Searches for documents containing inflectional forms of the word hardware but not the word bracket. +clamp Searches for the word darn') without generating inflectional forms. ~seat Searches for thesaurus forms of the word seat Assemb* Searches for words that begin with the prefix assemb . <washer nut> Searches for documents that contain the words washer in close proximity to the word nut
Read more

@@rowcount

@@rowcount is a global variable reports the number of affected rows in the most recently executed statement. Just like @@error you need to save it in a local variable if you want to use the value later, since @@rowcount is set after each statement. Since with SET you can only assign variable at a time, you must use SELECT if you need to save both @@error and @@rowcount into local variables: SELECT @err = @@error, @rowc = @@rowcount (For this reason, I prefer to always use SELECT for variable assignment, despite Microsoft's recommendations to use SET.) In T-SQL it is not an error if, for instance, an UPDATE statement did not affect any rows. But it can of course indicate an error in your application, as it could be an error if a SELECT returns more that one row. For these situations, you can check @@rowcount and raise an error and set a return value, if @@rowcount is not the expected value.
Read more

Sql Index

Image
Indexes help us retrieve data from tables quicker. Let's use an example to illustrate this point: Say we are interested in reading about how to grow peppers in a gardening book. Instead of reading the book from the beginning until we find a section on peppers, it is much quicker for us to go to the index section at the end of the book, locate which pages contain information on peppers, and then go to these pages directly. Going to the index first saves us time and is by far a more efficient method for locating the information we need. The same principle applies for retrieving data from a database table. Without an index, the database system reads through the entire table (this process is called a 'table scan') to locate the desired information. With the proper index in place, the database system can then first go through the index to find out where to retrieve the data, and then go to these locations directly to get the needed data. This is much faster.
Read more