This is one of two articles about error handling in SQL Server. This article
gives you recommendations for how you should implement error handling when you
write stored
procedures, including when you call them from ADO. The other article,
Error Handling in SQL Server – I,
gives a deeper description of the idiosyncrasies with error handling in SQL Server
and ADO. That article is in some sense part one in the series. However, you can read this
article without reading the background article first, and if you are not a very
experienced user of SQL Server, I recommend you to start here. In places
there are links to the background article, if you want more information about a
certain issue.
Note: this article is aimed at SQL 2000 and earlier versions of SQL Server. SQL 2005 offers significantly improved methods for error handling with TRY-CATCH, and I hope to write an article on error handling in SQL 2005 in the future. I have a brief section on SQL 2005 in the background article.
Table of Contents:
Introduction
The Presumptions
A General Example
Checking Calls to Stored Procedures
The Philosophy of Error Handling
General Requirements
Why Do We Check for Errors?
When Should You Check @@error?
ROLLBACK or not to ROLLBACK – That's the Question
SET XACT_ABORT ON revisited
Error Handling with Cursors
Error Handling with Triggers
Error Handling with User-Defined Functions
Error Handling with Dynamic SQL
Error Handling in Client Code
What to Do in Case of an Error?
Command Timeouts
Why is My Error Not Raised?
Getting the Return Value from a Stored Procedure
Acknowledgements and Feedback
Revision History
Introduction
Error handling in stored procedures is a very tedious task, because T-SQL offers no
exception mechanism, or any On Error Goto. All you have is the
global variable @@error which you need to
check after each statement for a non-zero value to be perfectly safe. If you
call a stored procedure, you also need to check the return value from the
procedure.
In fact, this is so extremely tedious, so you will find that you will have to
make compromises and in some situations assume that nothing can go wrong.
Still, you cannot just ignore checking for errors, because ignoring an
error could cause your updates to be incomplete, and compromise the integrity of your
data. Or it can cause a transaction to run for much longer time than intended,
leading to blocking and risk that the user loses all his updates when he logs out.
In the first section, I summarize the most important points of the material in the
background article, so you know under which presumptions you have to work.
Next, I show you a general example that covers the most essential parts of how to
do error handling, which I follow with the special considerations when you
call a stored procedure. I then wander into a section where I discuss some
philosophical questions on how error handling should be implemented; this is a
section you can skip if you are short on time. I recommend that you read the
section When Should You Check @@error,
though. I take a look at SET XACT_ABORT ON,
which can simplify your error handling – but not as much as you might hope. I then
look at error handling for four special areas: cursors, triggers, user-defined
functions and dynamic SQL. Finally, I look at error handling in client code,
with focus on ADO and ADO .Net.
To save space, I am focusing on stored procedures that run as part of
an application. I am not covering loose SQL statements sent from a client, and I
disregard administrative scripts like scripts for backup or scripts that
create or change
tables. Neither do I consider distributed transactions, nor situations
where you use SAVE TRANSACTION.
I'm not discussing different versions of SQL Server. The recommendations are
based from how SQL 2000 works, but they apply equally well to SQL 7 and
SQL 6.5. (The situation in SQL 6.5 is actually slightly less complex, but
since you presumably will move to SQL 7 or SQL 2000 you might as well write
your error handling for these versions directly.) As noted above
SQL 2005 offers new options for error handling which are much easier to use.
The Presumptions
This is a brief summary of the presumptions for implementing error handling
in T-SQL. The points below are detailed in the
background article, but here we just
accept these points as the state of affairs.
- After each statement, SQL Server sets
@@error to 0 if the statement was
successful. If the statement results in an error, @@error holds the
number
of that error. Because @@error is so volatile, you should always save
@@error to a local variable before doing anything else with it. - In some situations when an error occurs, SQL Server aborts the batch
and rolls back any open transaction, but for many errors SQL Server only terminates the statement where the error occurred, and it is your responsibility to roll back any transaction. Since SQL Server is not very consistent in which action it takes, your basic approach to error handling should be that SQL Server might permit execution to continue. - Yet an action SQL Server can take in case of an error, is to abandon execution of the current stored procedure, but return control to the calling procedure – without rolling back any transaction, even if it was started by the aborted procedure.
- The return value from a stored procedure should only serve to indicate whether the stored procedure was successful or not, by returning 0 in case of success, and a non-zero value in case of an error. You are the one who is responsible for that the procedure returns a non-zero value in case of an error.
- With SET XACT_ABORT ON, you can get SQL Server to abort the batch and rollback the transaction for most errors, but not all errors. Even if you use SET XACT_ABORT ON, you must at a minimum error-check calls to stored procedures.
A General Example
There is not any single universal truth on how to implement error handling in
stored procedures. There are several considerations on whether to roll back in all situations or
not, to use GOTO to an error label etc. Some of these considerations, I am
covering in this text. Some I have opted to stay silent on, since this text
is long enough already. Take what I present in this article as
recommendations. If they are in conflict with your common sense, it might be
your common sense that you should follow.
In this example I show how I implement error
checking in a stored procedure that creates a temp table, performs some
manipulation on the temp table, calls another stored procedure, and eventually starts a transaction to update
a couple of permanent tables. The procedure accepts a char(1) parameter for
which only certain values are permitted. In interest of brevity, I am only
outlining of the actual logic of the procedure.
CREATE PROCEDURE error_test_demo @mode char(1) AS
CREATE TABLE #temp (...)
DECLARE @err int,
...
IF @mode NOT IN ('A', 'B', 'C')
BEGIN
RAISERROR('Illegal value "%s" passed for @mode.', 16, -1, @mode)
RETURN 50000
END
INSERT #temp (...)
SELECT ...
SELECT @err = @@error IF @err <> 0 RETURN @err
UPDATE #temp
SET ...
FROM ...
SELECT @err = @@error IF @err <> 0 RETURN @err
EXEC @err = some_other_sp @value OUTPUT
SELECT @err = coalesce(nullif(@err, 0), @@error)
IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END
BEGIN TRANSACTION
INSERT permanent_tbl1 (...)
SELECT ...
FROM ...
SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END
UPDATE permanent_tbl2
SET ...
FROM #temp ....
SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END
DELETE permanent_tbl3
WHERE ...
SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END
EXEC @err = one_more_sp @value
SELECT @err = coalesce(nullif(@err, 0), @@error)
IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END
COMMIT TRANSACTION
SELECT @err = @@error IF @err <> 0 RETURN @err
Comments on various points:
Atomicity of transactions: Without all these tests on
@@error, an error in the UPDATE statement could lead to that we committed a transaction
that only included the result of the INSERT and DELETE
statements and whatever update one_more_sp performs. New
users to SQL Server are sometimes shocked when they find out the state of
affairs, since they have been taught that transactions are atomic. And
in theory they are right, but this is how SQL Server works. (And there is no
reason to feel stupid if you held this belief. Many years ago, this was an unpleasant
surprise to me
as well.)
Always save @@error into a local variable. Even if you can write error checking without
any local variable, you would still have need for it as soon you want to do something "fancy", so
you better
always use it. (To repeat: @@error is set after each
statement, so the snippet IF @@error <> 0 SELECT @@error
will never return anything but 0, because the IF statement is successful. See
also the background article for
an example.)
Exit on first error. As soon as there is an error, I abandon the
rest of the procedure and return a non-zero value to the caller. No attempt
to recovery or local error handling, not even an error exit. Keep it as
simple as possible. There are situations where you might want to have some
alternate action in case of error, for instance set a status column in some
table. In such case, you would use an IF @err <> 0 GOTO err_handle, but
in my experience this is too uncommon to warrant using GOTO in all cases. (There is
one generic situation where my proposed strategy needs some modification, and that
is when you use cursors, which we will look at later.)
Rollback or not. In the example, when I perform an SQL statement
outside my own transaction I don't include an explicit ROLLBACK TRANSACTION,
but I do it inside my transaction. When I call a stored procedure, I always
have a ROLLBACK. This may seem inconsistent, but for the moment take this a
fact. I discuss the issue further in the next section and in the section
ROLLBACK or not to ROLLBACK.
Error check on stored procedures. You may be bewildered by the complex
expression. The point is that you must check @@error as well as the return value from
the procedure. We will look closer at this in the next section.
Assertion. Notice the initial check for @mode where I raise
an error in case of an illegal mode and exit the procedure with a non-zero value
to indicate an error. Overall, it is a good recommendation to validate your input
data, and raise an error if data is something your code does not handle. Particularly
this is important, if the procedure is of a more
general nature that could be called from many sources. This is a programming
technique that also is used in traditional languages, and these checks are
generally known as assertions.
Return value. You can see that I am returning the actual error code,
and 50000 for the RAISERROR. This is basically a habit I have. I don't think
there are many places in our application that the caller
would actually look at it. So you can return 1, 4711 or whatever as long is
not zero. (One
strategy I applied for a while was that the first RETURN returned 1, next
returned 2 and so on, with the idea that the return value would identify
where things went wrong. I cannot recall that I ever had any real use for it,
though.)
Formatting. The formatting of the error checking merits a comment. The idea is that I
want the error checking as un-intrusive as possible so that the
actual mission of the procedure is not obscured. Thus, I put all on one
long line, and attach it directly to the statement I am checking, as
logically I see the
error checking as part of that statement. The checking for the
stored procedure is on two lines, though, since else that line would be very
long.
Checking Calls to Stored Procedures
When checking a call to a stored procedure, it is not sufficient to check
@@error. By the time execution returns to the caller, @@error may
again be 0, because the statement that raised an error was the not last the
one executed. For instance, if the DELETE statement in
error_demo_test
above fails on a constraint violation, the last statement the procedure executes is
RETURN @err, and this is likely to be successful.
This is where the careful use or the RETURN statement comes in: If you get a
non-zero value back from a stored procedure, this indicates that an error occurred in
that procedure or a procedure it called in its turn. And unless you
have any special error handling, or have reasons to ignore any error, you
should back out yourself.
But neither is checking the return value enough. If the invocation of the
procedure as such fails, for instance because of incorrect parameter count,
SQL Server does not set the return value at all, so that variable retains its old value.
And if
you are like me and use the same variable throughout your procedure, that
value is likely to be 0.
This is why in error_test_demo, I have this somewhat complex check:
EXEC @err = some_other_sp @value OUTPUT
SELECT @err = coalesce(nullif(@err, 0), @@error)
IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END
You may ask what does that on line 2 mean? The nullif function says
that if @err is 0, this is the same as NULL. coalesce is a function that returns the
first non-NULL value in its argument. That is, if the procedure returned a
non-zero return value, we use that value, else we use @@error. If any of
them has a non-zero value, an error has occurred somewhere.
As I noted in the previous section, I suggest that you always have a ROLLBACK
TRANSACTION if a call to a stored procedure results in error. This is
because the procedure may start a transaction that it does not commit.
This can happen either because there is a BEGIN TRANSACTION without a
matching COMMIT
or ROLLBACK TRANSACTION being executed, or because an error causes SQL Server to abort execution of the
procedure because of a compilation error that was not detected when you loaded the procedure because of deferred name
resolution. See the discussion on
scope-aborting errors in the background article for an example. I discuss
ROLLBACK more in the section ROLLBACK or not to
ROLLBACK.
Note: if you are calling a remote stored procedure, the return value will be
NULL, if the remote procedure runs into an error that aborts the batch. I
would expect @@error to have a non-zero value in this situation, but if you
are really paranoid, you can do something like this:
EXEC @err = REMOTESRV.db.dbo.remote_sp @value
SELECT @err = coalesce(nullif(@@error, 0), @err, -4711)
As for whether you should roll back in this situation or not, I have to admit
that I have not analysed this.
Finally, while most system procedures that come with SQL Server obey to the principle of returning 0
in case of success and a non-zero value in case of failure, there are a few exceptions. Here I only
mention one: sp_xml_removedocument, which returns 1 in all situations, so
for this procedure you should only check
@@error (I believe Microsoft has acknowledged this as a bug.) For other system
procedures: when in doubt, consult the
documentation for the system procedure in question in Books Online.
The Philosophy of Error Handling
In this section, I try to give a rationale for error handling
I recommend and try to cover what trade-offs you may be forced to when you
implement your own error handling. This section is somewhat
philosophical in nature, and if all you want is a cookbook on error handling,
feel free to move to the next section (about SET XACT_ABORT
ON). You may however want to study the sub-section
When Should You Check @@error.
General Requirements
In an ideal world, this is what we would want from our error handling:
- Simplicity. Error handling must be simple. If the error handling is
too complex, bugs might creep into the error handling, and what is the
likelihood that every single piece of error-handling code is tested?
Particularly, when error-handling appears after each statement? - Incomplete transactions must never be committed. This is a coin
with two sides. 1) When an error occurs in a statement, you
should somewhere issue a ROLLBACK TRANSACTION if there was an open
transaction. 2) If a user-defined transaction has been rolled back, you must not continue with the processing,
because you would only carry out the second part of the
transaction. - You must not leave incomplete transactions open. There are
situations where, if you are not careful, you could leave the process with
an open transaction. When the user continues his work, he will acquire
more and more locks as he updates data, with increased risk for blocking
other users. When he eventually disconnects, a big fat ROLLBACK sets
in and he loses all his changes. - Modularity, take one. A stored procedure should not assume
that just because it did not start a transaction itself, there is no
transaction active, as the calling procedure or client may have started a
transaction. - Modularity, take two. Ideally, a stored procedure should not
roll back a transaction that was started by a caller, as the caller may
want to do some recovery or take some other action. - Avoid unnecessary error messages. If you rollback too much, or
rollback in a stored procedure that did not start the transaction, you
will get the messages 266 – Transaction count after EXECUTE indicates that a
COMMIT or ROLLBACK TRANSACTION statement is missing – and 3903 – The
ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION -
which become white noise in the error-message output and occludes the real
error.
These requirements tend to conflict with each other, particularly the
requirements 2-6 tend to be in opposition to the requirement on simplicity. The order above roughly reflects
the priority of the requirements, with the sharp divider going between the
two modularity items. Actually, my opinion is that trying to address the very
last point on the list, would incur too much complexity, so I almost
always overlook it entirely.
It may baffle some readers that I have put simplicity on the top of the
list, but the idea is that if your error handling is too complex, then you run
the risk of messing up the transaction just because of errors in the
error handling.
Why Do We Check for Errors?
This question may seem to have an obvious answer, but it is worth considering
this question in some detail, to get a deeper understanding of what we are
trying to achieve.
The answer is that we don't want to continue execution after an error,
because we are likely to have incorrect data, and thus it is likely that the
execution will yield an incorrect result, either in terms of incorrect data
being returned to the client, or database tables being incorrectly
updated.
If you look at error_test_demo above, you can
easily see if we get an error in one the statements between the
BEGIN and COMMIT TRANSACTION, the transaction will be incomplete if we don't
check @@error and roll back and exit on an error. For instance, we may delete the old data, without inserting any
new. But it is also important to check the manipulation of the temp table
before the transaction starts, because if any of these operations fail, the
INSERT, UPDATE and DELETE in the transaction will operate from the wrong data
with unknown consequences.
Consider this outlined procedure:
CREATE PROCEDURE error_test_select @mode char(1) AS
CREATE TABLE #temp (...)
DECLARE @err int,
...
IF @mode NOT IN ('A', 'B', 'C')
BEGIN
RAISERROR('Illegal value "%s" passed for @mode.', 16, -1, @mode)
RETURN 50000
END
INSERT #temp (...)
SELECT ...
SELECT @err = @@error IF @err <> 0 RETURN @err
UPDATE #temp
SET ...
FROM ...
SELECT @err = @@error IF @err <> 0 RETURN @err
SELECT col1, col2, ...
FROM #temp
JOIN ...
As you see the initial part is similar to error_test_demo, but instead of a transaction, there
is a SELECT statement that produces a result set. We still check for errors, so
that we don't go on and produce a result set with incorrect data.
You may note that the SELECT statement itself is not followed by
any error checking. I will discuss this in the next section.
When Should You Check @@error?
After any statement in which an error could affect the result of the stored
procedure, or a stored procedure that has called it. And that is about any
statement in T-SQL.
In practice, this is not really workable. These are the
statements for which I recommend you to always check @@error:
- DML statements, that is, INSERT, DELETE and UPDATE, even when they affect
temp tables or table variables. - SELECT INTO.
- Invocation of stored procedures.
- Invocation of dynamic SQL.
- COMMIT TRANSACTION.
- DECLARE and OPEN CURSOR.
- FETCH from cursor.
- WRITETEXT and UPDATETEXT.
SELECT is not on this list. That does not mean that I like to discourage your
from checking @@error after SELECT, but since I rarely do this myself, I felt
I could not put it on a list of recommendations. SELECT can occur in three
different situations:
- Assignment of local variables. (This also includes of SET for the
same task). You can run into errors like overflow or permissions problems,
that would cause the variables to get incorrect values, and thus highly likely to
affect the result of the stored procedure. When in doubt, check @@error. - Producing a result set. Often a SELECT that produces a result set
is the last statement before control of execution returns to the client,
and thus any error will not affect the execution of T-SQL code. The client
does need any non-zero return value, since it sees the error itself. (You
can never hide an error from a client.), and
hopefully understand that the result set is not to be trusted, should rows
have been returned before the error occurs.
The construct
INSERT-EXEC permits you to insert the output of a stored procedure into a table
in the calling procedure. In this case it would be best to check @@error and
set return status after the SELECT. Problem is, you can
never tell if someone decides to call your procedure with
INSERT-EXEC. This
construct is not that common, and personally I discourage use of it.
(Follow the link to it, to see why.) I'm inclined to say that it is up to
the developer who decides to call your procedure with
INSERT-EXEC to make sure that he
gets correct error information back. - Conditional tests for IF and WHILE. This is where things
definitely get out of hand. For starters, where to you put the check of @@error?
(You put it where execution would end up if the condition does not
yield a true value. With the error checking a long way from what it
checks, you get quite obscure code. )
Workaround: write IF and WHILE with SELECTs that are so simple that they
cannot go wrong. Or save result of the test into a local variable, and check
@@error before the conditional.
Here I have not covered DDL statements (CREATE VIEW etc) or DBA statements like
BACKUP or DBCC. They are not in the scope for this article, since I am
restricting myself to application development. Only two DDL statements are likely to appear in application code: CREATE and DROP
TABLE for temp tables. It seems that if there is an error in a CREATE TABLE
statement, SQL Server always aborts the batch. Were execution to continue, it is
likely that any reference to the table would cause an error, since the table
never was created. Thus, I rarely check @@error after
CREATE TABLE.
A note on COMMIT TRANSACTION: the one error that could occur with COMMIT
TRANSACTION is that you do not have a transaction in progress. In itself this is
not likely to affect the continued processing, but it is a token of that
something has already gone wrong, why it is best to back out, so that you do not
cause more damage.
Note: whereas I cover most of the statements above in one way or another in this
text, I am not giving any further coverage to text/image manipulation with
READTEXT, WRITETEXT and UPDATETEXT, as I have little experience from working
with these.
Finally, keep in mind that these are these recommendations covers the general
case. There are situations when checking @@error is unnecessary, or even
meaningless. This is when you basically have nowhere to go with the error.
I'll show you an example of this when we look at error handling with cursors.
ROLLBACK or not to ROLLBACK – That's the Question
You saw in error_test_demo that I did only issue
a ROLLBACK when 1) I had started a transaction myself or 2) I had called a
stored procedure. In this section, I will further discuss when to roll back
and not.
The quick answer on when to roll back is that if you want
maximum simplicity: whenever you get a non-zero value in @@error or a
non-zero return value from a stored procedure, your error checking should include a ROLLBACK TRANSACTION, even if
there is no transaction in sight in your stored procedure. In such case you
are taking care of the first four of the general requirements:
#1 Simple.
#2
ROLLBACK on first error. #3 Do not leave transactions open. #4 Caller may
have started a transaction. But you are ignoring the last two requirements:
#5 The scope that started the transaction should also roll it back and #6 Avoid unnecessary error messages.
I have already said that I don't care about #6. If you find the extra
error messages annoying, write your error handling in the client so that it
ignores errors 266 and 3903 if they are accompanied by other error messages.
(If they appear on their own, they indicate that you have an error in the
application, so then you should not drop them on the floor.)
It is in an attempt to respect #5 – let the scope that started the transaction
also
roll it back – that I in
error_demo_test do not issue a ROLLBACK when I have not started a transaction myself. But it is only
half-hearted, because when I call a stored procedure, I always roll back,
since the procedure I called may have started a transaction but not rolled it
back as I discussed above. I cannot trust the guy who
called me to roll it back, because if he had no transaction in progress he
has as much reason as I to roll back. Thus I have to sacrifice #5 in order to
save the more important requirement #3 – don't leave transactions open.
To fully respect point #5, we would
have to save @@trancount in the beginning of the procedure:
CREATE PROCEDURE error_test_modul2 @mode char(1) AS
CREATE TABLE #temp (...)
DECLARE @err int,
@save_tcnt int
...
SELECT @save_tcnt = @@trancount
...
EXEC @err = some_other_sp @value OUTPUT
SELECT @err = coalesce(nullif(@err, 0), @@error)
IF @err <> 0 BEGIN IF @save_tcnt = 0 ROLLBACK TRANSACTION RETURN @err END
BEGIN TRANSACTION
INSERT permanent_tbl1 (...)
SELECT ...
FROM ...
SELECT @err = @@error IF @err <> 0 BEGIN IF @save_tcnt = 0 ROLLBACK TRANSACTION RETURN @err END
Personally, I feel that this violates the simplicity requirement a bit too
much to be acceptable, but as they say, you mileage may vary.
When calling a
procedure you may "know" that this is a read-only procedure, and therefore
cannot leave you with a stray transaction. However, this thinking is somewhat
dangerous. What if some developer next year decides that this procedure
should have a BEGIN TRANSACTION? Overall, the less you assume about the code
you call, the better.
There is a special case where you can skip the ROLLBACK entirely, even
for
error-checks of calls to stored procedures:
CREATE PROCEDURE error_test_inner @mode char(1) AS
IF @@trancount = 0
BEGIN
RAISERROR ('This procedure must be called with a transaction in progress', 16, 1)
RETURN 50000
END
INSERT permanent_tbl1 (...)
SELECT ...
FROM ...
SELECT @err = @@error IF @err <> 0 RETURN @err END
This procedure has an assertion that checks that there is an active transaction
when the procedure is invoked. This may be an idea that is new to you, but I
have written more than one procedure with this check. Such a
procedure is part of a larger operation and is a sub-procedure to
a main procedure. It would be an error to perform only the updates in this
procedure. (Such procedures also commonly check @@nestlevel.) Since we know that
the caller has an active transaction, we also trust it to handle the rollback
for us.
Before I close this section, I should add that I have made the tacit assumption
that all code in a set of a nested procedures is written within the same
organisation where all programmers obey to the same error-handling principles.
If your procedure might be called by programmers in a different town in a
different country, you need to take extra precautions. Not the least do you need to document how you handle transactions
in case of an error.
SET XACT_ABORT ON revisited
One way to make your error handling simpler is to run with SET XACT_ABORT ON.
With this setting, most errors abort the batch. This may give you the idea that
you don't need any error handling at all in your stored procedures, but not so
fast! I said most errors, not all errors.
Even if XACT_ABORT is ON, as a minimum you must check for errors when calling
stored procedures, and when you invoke dynamic SQL. This
is because XACT_ABORT does not affect compilation errors, and compilation errors
are typically those that cause SQL Server to abandon execution of a procedure
and return control to the caller. Nor will the batch be aborted because of a RAISERROR, so if you detect an error condition, you still need to return a
non-zero value to the caller, that has to check for it.
Also, when XACT_ABORT is ON, error 266, Transaction count after EXECUTE
indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing, does
not abort the batch. This is not documented in Books Online, and it makes me a
little nervous that there might be more errors that SET XACT_ABORT ON does
not affect.
In any case,
I would suggest that if you use SET XACT_ABORT ON, you should use it
consistently, preferably submitting the command from the client directly on
connection. What you should not do, is
to use it sometimes and sometimes not. Particularly it is bad, if you as an
individual programmer as your private standard insert a SET XACT_ABORT ON in the
procedures you write, while your colleagues do not. Say that another programmer calls your
code. He might have
some error-handling code where he logs the error in a table. While SQL Server may
abort the batch for some errors, sufficiently many errors let execution
continue to make such a scheme worthwhile. As long as not any joker starts to
play games with SET XACT_ABORT ON, that is. (Note: there are some
situations with distributed queries where SET XACT_ABORT ON is required for them
to work. This is the exception to the rule that you should not use XACT_ABORT ON
sometimes.)
Error Handling with Cursors
When you use cursors or some other iterative scheme, there are some special
considerations for error handling. Some of this due to the nature of
cursors as such, whereas other issues have to with the iteration in general.
You create a cursor with the DECLARE CURSOR statement, which despite the name
is an executable statement. A cursor can be either process-global or local to
the scope where it was created. The default is process-global, but. I recommend that you use local cursors,
which you specify by adding the keyword LOCAL after the keyword CURSOR.
However, you cannot use local cursors if you create the cursor from dynamic
SQL, or access the cursor from several procedures or from dynamic SQL.
If you apply the standard error handling we have used this far with a
process-global cursor, you will leave the cursor as existing and open. Next
time the same process calls the procedure, you will get an error saying that
the cursor already exists and is open. If you ignore the error, the
cursor will continue where you left it last time, although the input
parameters say that a completely different set of data should be handled.
That's bad.
To deal with this, you need this error-checking code for a global cursor:
DECLARE some_cur CURSOR FOR
SELECT col FROM tbl
SELECT @err = @@error IF @err <> 0 BEGIN DEALLOCATE some_cur RETURN @err END
That is, if DECLARE CURSOR fails, issue a statement to deallocate the cursor.
(I did not include ROLLBACK TRANSACTION here, but this may be a good idea,
as I discussed in the previous section.)
In the cursor loop, assuming that you want to abort the procedure on first
error, this is how you should do it:
OPEN some_cur
WHILE 1 = 1
BEGIN
FETCH col INTO @var
SELECT @err = @@error IF @err <> 0 BREAK
IF @@fetch_status <> 0
BREAK
UPDATE some_tbl
SET ...
SELECT @err = @@error IF @err <> 0 BREAK
...
END
DEALLOCATE some_cur
IF @err <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN @err
END
...
That is, when running a global cursor you cannot exit immediately, but you must
first make sure that the
cursor is closed and deallocated. Once this has been done, you can check @err, and leave the procedure.
At this point, it is safest to always include a ROLLBACK TRANSACTION, as we no
longer know at which point the error occurred, and there could have been a
transaction in progress there.
In passing, note here how I write the cursor loop with regards to FETCH. This
style with a single FETCH statement is highly recommendable, because if you
change the column list in the cursor declaration, there is only one FETCH to
change, and one possible source of error less.
When you do iterative
processing, there are cases when you do not want to exit the procedure on first error.
You go through a set of rows that are handled independently, and if
an operation fails for one row, you may still want to try to process
remaining rows, possibly setting an error flag for the failed row. Here is an
outline of such a procedure may look like:
CREATE PROCEDURE error_demo_cursor AS
DECLARE @err int,
...
IF @@trancount > 0
BEGIN
RAISERROR ('This procedure must not be called with a transaction in progress', 16, 1)
RETURN 50000
END
DECLARE some_cur CURSOR FOR
SELECT id, col1, col2, ...
FROM tbl
WHERE status = 'New'
...
SELECT @err = @@error IF @err <> 0 BEGIN DEALLOCATE some_cur RETURN @err END
OPEN some_cur
SELECT @err = @@error IF @err <> 0 BEGIN DEALLOCATE some_cur RETURN @err END
WHILE 1 = 1
BEGIN
FETCH some_cur INTO @id, @par1, @par2, ...
SELECT @err = @@error
IF @err <> 0 OR @@fetch_status <> 0
BREAK
BEGIN TRANSACTION
EXEC @err = some_sp @par1, ...
SELECT @err = coalesce(nullif(@err, 0), @@error) IF @err <> 0 GOTO Fail
INSERT other_tbl (...)
SELECT @err = @@error IF @err <> 0 GOTO Fail
UPDATE tbl
SET status = 'OK'
WHERE id = @id
SELECT @err = @@error IF @err <> 0 GOTO Fail
COMMIT TRANSACTION
SELECT @err = @@error IF @err <> 0 BREAK
-- Handle next guy
CONTINUE
Fail:
ROLLBACK TRANSACTION
UPDATE tbl
SET status = 'Error'
WHERE id = @id
-- No error-checking here.
END
DEALLOCATE some_cur
RETURN @err
Here, if we get an error while we are handling the row, we don't want to exit
the procedure, but only set an error status for this row and then move on to
the next. The particular UPDATE statement where we set the status to 'Error' has
no error checking, because – well, there is not really any action we can take if
this UPDATE fails. If
we for some reason cannot set the status, this is not reason to abort the
procedure. As you see, there is a comment that explicitly says that there
is no error checking, so that anyone who reviews the code can see that the
omission of error checking is intentional.
If you look closer, you see that in some cases we abort the procedure in
case of an error even within the loop. We do so for FETCH, because the most
likely error with a FETCH statement is a mismatch between the
variables and the column list in the cursor. In this case, all executions of
the FETCH statement will fail, so there is no reason to hang around. A
similar reasoning applies when it comes to COMMIT TRANSACTION. Errors with
COMMIT are so unexpected, that if they occur we have very little idea of what
is going on, why the best is to leave here and now.
I've also added an assertion to disallow the caller
to have an open transaction when calling error_demo_cursor. If we were to
start with an open transaction, and there is an error with
the processing of the fourth element in the cursor, the processing of the
first three will be rolled back. Since the idea that we want rows committed
as we handle them, there is little reason to embed error_demo_cursor in a
transaction. (If you really need this, you could play with the obscure
command SAVE TRANSACTION, but I'm not going into details here.)
Finally, you can see that I permitted me to simplify the exit some, by just
saying RETURN @err, since I know that I cannot have any transaction
active at this point that was not active when error_demo_cursor was called.
Error Handling with Triggers
Triggers differ from stored procedures in some aspects. If you are lazy, you
can actually skip error checking in triggers, because as soon as an error
occurs in a trigger, SQL Server aborts the batch. With one exception: if you
raise an error yourself with RAISERROR, the batch is not aborted. However, if
you issue a ROLLBACK TRANSACTION, the batch is aborted when the trigger exits.
So here is how you would do:
IF EXISTS(SELECT *
FROM inserted i
JOIN deleted d ON d.accno = i.accno
WHERE d.acctype <> i.acctype)
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Change of account type not permitted', 16, 1)
RETURN
END
In the philosophical section, I had a
discussion on whether you should rollback or not.
These considerations do not apply in a trigger, but in a trigger you should
always roll back when you detect a breach against a business rule. Forget all ideas about not rolling
back someone
else's transaction. The reason for this is simple: In a trigger,
@@trancount is
always ≥ 1, because if there was no transaction in progress, the INSERT,
UPDATE or DELETE statement is its own transaction. But if you wrap the statement in an
explicit transaction, @@trancount is still 1 and not 2. So you don't have any
knowledge whether the caller have a transaction in progress or not.
Note also
a trivial
difference to stored procedures: the RETURN statement does not take
parameters in triggers.
If you are really paranoid, there is one check you may want to add to
triggers that call stored procedures. Normally, if you call a stored
procedure and it starts a transaction which it for some reason does not
commit or rollback, SQL Server raises error 266, Transaction count after
EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.
But for some reason, this error is not raised when the procedure is invoked
from a trigger. (It is documented in Books Online, so it is not a bug.) This
could lead to violation of general
requirement #3, don't leave transactions open. Consider this very stupid
example:
CREATE TABLE stray_trans_demo (a int NOT NULL)
go
CREATE PROCEDURE start_trans AS BEGIN TRANSACTION
go
CREATE TRIGGER stray_trans_trigger ON stray_trans_demo
FOR INSERT AS
EXEC start_trans
go
INSERT stray_trans_demo (a) VALUES (4711)
SELECT @@error, @@trancount
ROLLBACK TRANSACTION
The output is:
(1 row(s) affected)
----------- -----------
0 1
(1 row(s) affected)
Not an error in sight, but we came out of the INSERT statement with an open transaction with
all the problems it could lead to.
The remedy for this would be to save @@trancount in the beginning of the
trigger, and then compare this value against @@trancount after call to each
stored procedure, and raise an error and roll back in case there is a difference.
Note here that this situation can only occur because of a stray BEGIN
TRANSACTION. The other reason that a procedure may leave you with an orphan
transaction because it was aborted by an error is not an issue here, because
in trigger context, these errors do abort the batch.
Error Handling with User-Defined Functions
If an error occurs in a user-defined function (with the exception of
table-valued inline functions), this is very difficult for the caller to detect. Normally a UDF is
invoked as part of a query. When an error occurs in a UDF, execution of the
function is aborted immediately and so is the query, and unless the error is one that aborts the batch, execution
continues on the next statement – but @@error is 0!
If you want it waterproof, I can only see one way to go:
- Run with SET XACT_ABORT ON, so that SQL Server aborts the batch on most
errors. - To cover the compilation errors, that SET XACT_ABORT does not affect, use
WITH SCHEMABINDING in all your functions. With this option in effect, SQL
Server requires that all tables and views that the function refers to must
exist, and furthermore you cannot drop them, as long as the function
exists.
If you find this too heavy-duty, what are your choices?
Write simple functions that are simple to test and verify that they
absolutely cannot cause any error. If they use table variables, declare all
columns as nullable, so that you cannot get a NOT NULL error in the function.
If the UDF is used in an INSERT or UPDATE statement, you may get a NOT NULL
violation in the target table instead, but in this case @@error is set. For
the same reason,
don't use constraints in your table variables.
If the logic of your UDF is complex, write a stored procedure instead. This makes the
calling code a little clumsier, but multi-valued table functions are mainly
syntactic sugar. I have an article sharing
data between stored procedures that discusses this more in detail. As for
scalar functions, you should be wary to use them anyway, because they often
lead to serialization of the query leading to extreme performance penalties.
In all fairness, the risk for errors in user-defined function is smaller than in a
stored procedure, since you are limited in what you can do in a function.
Note: that the problems I have mentioned does not apply to table-valued
inline functions. These functions are basically macros that are pasted into
the query, so they are never called in the true sense of the word.
Note: you can invoke a scalar function through EXEC as well. In this
case, when an error occurs in the function, execution continues and you can
check @@error within the UDF. The problem with communicating the error to the caller remains,
as the caller will not see the value of @@error. You would have to define a
certain return value, for instance NULL, to indicate that an error occurred.
Error Handling with Dynamic SQL
If you invoke of a batch of dynamic SQL like this:
EXEC(@sql)
SELECT @@error
@@error will hold the
status of the last command executed in @sql. This means that if there was an
error in one of the statements in @sql, but other statements were executed after this
statement, @@error will be 0. Thus, here is a potential risk that an error goes
unnoticed.
But this only applies only if your dynamic
SQL includes several statements. I would suppose that most batches of
dynamic SQL consist of a single
SELECT command, in which case error-detection is not a problem.
Also, the most
likely errors from a batch of dynamic SQL are probably syntax errors. This means that these errors are not taken care of by SET XACT_ABORT ON.
So by all means, check @@error after all invocations of dynamic SQL.
If you use sp_executesql you also have a return value:
exec @err = sp_executesql @sql
select @@error, @err
However, the return value from sp_executesql appears to always be the final
value of @@error, so in practice you only have one value.
If you really run
multi-statement batches of dynamic SQL, use sp_executesql rather than EXEC
and use an @err OUTPUT parameter to indicate whether there was errors in the
batch, and check both this parameter and @@error. See my article on
dynamic SQL for an example of
using OUTPUT parameters with sp_executesql.
Error Handling in Client Code
Since the capabilities for error handling in T-SQL is limited, and you cannot suppress errors from
being raised, you have to somehow handle T-SQL errors in your client code too. There are plenty
of client libraries you can use to access SQL Server. Here I mainly
cover ADO and ADO .Net, since I would expect
these to be the most commonly used client libraries. I give more attention to
ADO, for the simple reason that ADO is more messy to use.
Note: I'm mainly an SQL developer. Therefore, I am not inclined to make any
distinction between "real" clients and middle-tiers. For me they are all
clients. For the same reason, my experience of ADO and ADO
.Net programming is not in par with my SQL knowledge . Therefore, I will be fairly brief and be
short on code samples. For more articles on error handling in .Net languages,
there is a good collection on
ErrorBank.com.
I will jump straight to what have you to take care of. If you want to know about
how ADO and ADO .Net handles errors in general, the accompanying
background article on error
handling has one section each on ADO
and ADO .Net.
What to Do in Case of an Error?
By now, you probably know that when calling a stored procedure from T-SQL,
the recommendation is that your error handling should include a ROLLBACK
TRANSACTION, since the stored procedure could have started a transaction that it
failed to roll back, because of a stray BEGIN TRANSACTION or because of an
error that aborted execution and returned control to the caller.
This applies when you call a stored procedure from a client as well. In
your error handling code, you should have something like this (example for
ADO):
If cnn Is Not Nothing Then _
cnn.Execute "IF @@trancount > 0 ROLLBACK TRANSACTION", , adExecuteNoRecords
Note: if you have started a transaction on ADO level with the
.BeginTrans method on the Connection object, you should probably
use the .RollbackTrans method rather than issuing an SQL batch.
In ADO .Net, there are ways to tell ADO .Net that
you want to immediately want to disconnect after a query. I have not explored
this, but I suppose that in this situation it may be difficult to issue a
ROLLBACK command. You may think that if you are disconnected, that you don't
have a problem, but see the next section about connection pooling.
Command Timeouts
Command timeout is an error that can occur only client level. Most client
libraries from Microsoft – ADO, ODBC and ADO .Net are all among them – have a
default command timeout of 30 seconds, so that if the library has not received any response from SQL Server within 30 seconds,
the client library cancels the SQL
command and raises the error Timeout Expired.
Nevertheless, it is very important that you handle a timeout error as you
would handle any other error from a stored procedure: issue IF @@trancount > 0 ROLLBACK TRANSACTION, (or
Connection.RollbackTrans). This is necessary because, if the procedure
started a transaction, neither SQL Server nor the client library will roll it back.
(There is one exception to this in ADO .Net: if you have associated a
transaction with the Connection object, ADO .Net will issue a
rollback.)
You may get the idea that you can skip the rollback, because you will close
the connection any way. But both ADO and ADO .Net (but not ODBC or
DB-Library) employs connection pooling, which means that when you close a
connection, ADO and ADO .Net keep it open for some 30-60 seconds in case the
application would reconnect. Once you reconnect, ADO and ADO .Net issue
sp_reset_connection to give you a clean connection, which includes rollback
of any open transaction. But on the moment you close the connection, nothing
at all happens, so the locks taken out during the transaction linger, and may
block other users.
All client libraries I know of, permit you to change the command timeout. In
ADO there is a .CommandTimeout property on the Connection and Command
objects. You need to set it on both objects; the Command object does
not inherit the setting from the Connection object. In ADO .Net, CommandTimeout is only on the Command object. My recommendation
is to set the timeout to 0 which means "no timeout", unless you have a clear
understanding what you want to use the timeout for.
Note: several of the issues that I have covered here, are also discussed in KB
article 224453, in the section Common Blocking Scenarios and
Resolution, point 2.
Why is My Error Not Raised?
Sometimes you see people on the newsgroups having a problem with ADO not
raising an error, despite that the stored procedure they call produces an
error message. Short answer: use SET NOCOUNT ON, but there are a few more
alternatives. To discuss them, I first need to explain what is going on:
Say you have a procedure like this one:
CREATE PROCEDURE some_sp AS
CREATE TABLE #temp (...)
INSERT #temp (...)
UPDATE #temp ...
SELECT ... FROM #temp
Assume that the UPDATE statement generates an error.
If you run the procedure from Query Analyzer, you will see something like:
(19 row(s) affected)
Server: Msg 547, Level 16, State 1, Procedure some_sp, Line 4
UPDATE statement conflicted with COLUMN CHECK ...
The statement has been terminated.
a
-----------
1
2
3
(3 row(s) affected)
But if you invoke the procedure from ADO in what appears to be a normal way, you will see nothing. No error, no result set.
The reason for this is that this procedure generates two recordsets. The first
recordset is a closed recordset, that only carries with it the 19 row(s)
affected message for the INSERT statement. It is not until you retrieve the next
recordset, the one for the UPDATE statement, that the error will be raised. This
is the way ADO works.
ADO .Net is different: here you do not get these extra recordsets. And
anyway, most often you use DataAdapter.Fill which does not return
until it has retrieved all data, and if there is an SQL error, it throws an
exception.
In ADO, there are several ways of handling this situation, and they can be
combined. (The next three sections apply to ADO only.)
SET NOCOUNT ON
This is the most important method. With SET NOCOUNT ON you instruct SQL
Server to not produce these rows affected messages, and the problem
vanishes into thin air. (Unless you generate a real result set, and then
produce an error in your stored procedure, but this is not a common
scenario.) Put this command in your stored procedure. You can also issue it
directly as you connect. Unfortunately, there is no way to get this into the
connection string, so if you connect in many places, you need to issue SET
NOCOUNT ON in many places. And, as if that is not enough, there are situations
when ADO opens a second physical connection to SQL Server for the same
Connection object behaind your back. This is an attempt to be helpful,
when you initiate an operation and there is unprocessed data on the
connection, but can be a real source for confusion.
If you don't have any code which actually retrieves the number of affected
rows, then I strongly recommend that you use SET NOCOUNT ON. Not only makes
it error handling easier, but you also gain performance by reducing network
traffic.
(You can even make SET NOCOUNT ON the default for your server, by
setting the configuration option useroptions, but I am
hesitant to recommend this. While the rows affected messages are
rarely of use in an application, I find them handy when running ad hoc
statements from Query Analyzer.)
.NextRecordset
You can continue to retrieve recordsets with Recordset.NextRecordset until you get Nothing in return. Once you have consumed
all the recordsets
that comes before the error, the error will be raised.
For me who has programmed a lot with DB-Library this is a natural thing to
do. But more experienced ADO programmers has warned me that this causes
round-trips to the server (which I have not been able to detect), and this
does not really seem to be the ADO way of thinking. I still like the idea
from the perspective of robust programming. What if your stored procedure has
a stray result set, because of a debug SELECT that was accidentally left
behind? Then again, I have noticed that with some server-side cursor types,
.NextRecordset does not always seem to be supported.
adExecuteNoRecords
You can specify this option in the third parameter to the .Execute methods of the Connection and Command objects. This option
instructs ADO to discard any result sets. Obviously, this is not a good idea
if you want data back. But if you
have procedure which only performs updates to the database, this option gives
some performance improvement by discarding the rows affected messages. And since there are no recordsets, any errors from the stored
procedure are raised immediately.
Getting the Return Value from a Stored Procedure
When checking for errors from a stored procedure in T-SQL, we noted that it
is important to check both the return status and @@error. When you have
called a stored procedure from a client, this is not equally interesting, because
any error from the procedure should raise an error in the client code, if not
always immediately.
Nevertheless, if you want to get the return value, this is fairly straightforward.
In ADO, you use
the .Parameters collection, and use the parameter 0 for the return
value. For Parameter.Direction you specify adParamReturnValue.
If you use a client-side cursor, you can retrieve the return value at any
time. But if you use a server-side cursor, you must first retrieve all
recordsets, before you can retrieve the return value.
The procedure for getting the return value is similar in ADO .Net. If you use
ExecuteReader, you must first retrieve all rows and result sets for
the return value to be available. Beware that the OleDb and Odbc .Net Data Providers, do not always
provide the return value, if there was an errur during the execution of the
procedure.
Acknowledgements and Feedback
Thanks to Thomas Hummel who pointed out a weakness in error_demo_cursor.If you have technical questions that any knowledgeable person could answer, I encourage you to post to any of the newsgroups microsoft.public.sqlserver.programming or comp.databases.ms-sqlserver.For more articles error-handling in .Net, check out ErrorBank.com
An SQL text by Erland Sommarskog, SQL Server MVP.
No comments:
Post a Comment