Handling Errors

for RuBoard

Of all the things that frustrate and bewilder Transact-SQL developers, error handling is probably at the top of the list. There are several reasons for this. Chief among them is that Transact-SQL's error handling simply doesn't work properly in some circumstances. It either doesn't work as documented or the way it works doesn't make any sense. Let's begin by talking about how error handling is supposed to work in Transact -SQL.

@@ERROR

When an error occurs, @@ERROR normally contains its error number. Your code should check @@ERROR after significant operations. Listing 7-1 is an example of an error and the type of T-SQL code that's needed to handle it:

Listing 7-1 Sample error handling code.
 USE Northwind DECLARE @c int SELECT @c=COUNT(*) FROM ORDERS WHERE OrderDate = '20001201' SELECT 1/@c IF @@ERROR<>0 -- Check for an error   PRINT 'Zero orders on file for the specified date' 

(Results)

 Server: Msg 8134, Level 16, State 1, Line 4 Divide by zero error encountered. Zero orders on file for the specified date 

Put aside for the moment that the error could be avoided altogether. Instead, look at the pattern being used here: We perform an operation, check a global error status variable, then react accordingly . This is the way that errors are usually handled in Transact-SQL because the language has no structured exception handling or ON ERROR GOTO-like syntax. You might call this Transact-SQL's basic Error Handler pattern.

User Errors

According to the Books Online, errors with a severity of 11 through 16 are user errors and can be corrected by the user. Let's purposely cause one of these and see whether we can recover from it (Listing 7-2):

Listing 7-2 Some errors terminate the current command batch.
 USE Northwind GO SELECT * FROM MyTable IF @@ERROR<>0 BEGIN   PRINT 'Creating table...'   CREATE TABLE MyTable   (c1 int)   INSERT MyTable DEFAULT VALUES   SELECT * FROM MyTable END 

This code attempts to list a table, and if it gets an error, attempts to create the table and fill it with data because it assumes the error was caused by the table not existing. Here's the output from the query:

 Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'MyTable'. 

Notice the code that checks @@ERROR is never reached. Why? Because error 208 (Invalid object name) terminates the current command batch. The error-handling code is never reached, so it's impossible for our code to handle the error.

You might think that this is because the error has a severity of 16, but consider the code in Listing 7-3:

Listing 7-3 Not all severity-16 errors terminate the command batch.
 RAISERROR('Table not found',16,1) IF @@ERROR<>0 BEGIN   PRINT 'Creating table...'   CREATE TABLE MyTable   (c1 int)   INSERT MyTable DEFAULT VALUES   SELECT * FROM MyTable   DROP TABLE MyTable END 

(Results)

 Server: Msg 50000, Level 16, State 1, Line 1 Table not found Creating table... c1 ----------- NULL 

Here, even though we raise an error with a severity level of 16, the batch isn't interrupted . Our error-handling code is reached and the table is created.

Perhaps you'd speculate that the code following the RAISERROR in Listing 7-3 is reached because the error wasn't the result of a true errorwe forced it via RAISERROR. Have a look back at Listing 7-1. What we see there is a real error. We've divided by zero, and its severity is 16, yet we're able to handle the error. Some severity-16 errors terminate the current batch; some don't. There seems to be little rhyme or reason as to which ones do and which ones do not.

So there you have it: A bona fide quirk in Transact-SQL error handling. What can you do about it? Here's a workaround for handling errors that terminate the current command batch (Listing 7-4):

Listing 7-4 A workaround for errors that terminate the command batch.
 USE Northwind GO DECLARE @res int EXEC @res=sp_executesql N'SELECT * FROM MyTable' IF @res<>0 BEGIN   PRINT 'Creating table...'   CREATE TABLE MyTable   (c1 int)   INSERT MyTable DEFAULT VALUES   SELECT * FROM MyTable   DROP TABLE MyTable END 

(Results)

 Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'MyTable'. Creating table... c1 ----------- NULL 

By executing the suspect SQL using sp_executesql, we isolate it from the calling routine and prevent the error from terminating the procedure. The sp_executesql procedure can also come in quite handy for dealing with linked server errors. Linked server errors almost always terminate the current command batch. If a linked server with which you're communicating is a SQL Server, you can query it via sp_executesql to isolate the call from the rest of your code and prevent it from terminating the batch should an error occur. Here's an example:

 EXEC MyOtherServer.master.dbo.sp_executesql N'SELECT * FROM sysdatabases' 

Calling sp_executesql in this manner forces the query to run on the other server. This should generally help performance, especially when you're traversing lots of data on the remote server.

Even when you're not connecting to another SQL Server, sp_executesql can still be of help:

 EXEC sp_executesql N'SELECT * FROM MyOtherServer.Northwind.dbo.MyTable' 

The benefit of using sp_executesql here is the isolation it affords. Even if the query does not run completely on the other server, at least any errors produced won't terminate the current batch.

In this scenario, sp_executesql's return value will be the error code, if there is one. @@ERROR should also contain the error code. So, for example, you could easily convey the error code back up through the call stack like this (Listing 7-5):

Listing 7-5 It's easy enough to convey the error message back up the chain.
 CREATE PROC ListTable AS DECLARE @res int EXEC @res=sp_executesql N'SELECT * FROM MyTable' IF @res<>0 BEGIN   PRINT 'Error listing table.'   RETURN(@res) END GO DECLARE @r int EXEC @r=ListTable SELECT @r 

(Results)

 Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'MyTable'. Error listing table. ----------- 208 

As you can see, we get the error message and handle it, then convey the error code back to the caller.

Note that you need not call sp_executesql to use this technique. A plain subroutine will do as well (Listing 7-6):

Listing 7-6 When an error causes a procedure to exit, @@ERROR contains the error code.
 CREATE PROC ListTable AS SELECT * FROM MyTable GO EXEC ListTable IF @@ERROR<>0 BEGIN   PRINT 'Creating table...'   CREATE TABLE MyTable   (c1 int)   INSERT MyTable DEFAULT VALUES   SELECT * FROM MyTable   DROP TABLE MyTable END Server: Msg 208, Level 16, State 1, Procedure ListTable, Line 3 Invalid object name 'MyTable'. Creating table... c1 ----------- NULL 

Here, rather than check the subprocedure's result code, we simply check @@ERROR immediately after returning from the routine. If an error caused the routine to exit, @@ERROR will be nonzero.

Note that we couldn't return the error from ListTable as a result code even if we wanted to. Again, once the error occurs, all bets are off. The procedure exits immediately and returns to the caller.

Fatal Errors

As I've mentioned, errors with a severity of 20 or more are considered fatal and terminate the connection. What can you do about them? One workaround would be to use the xp_exec routine included in Chapter 20 to run the problematic code on a separate connection that shares the current connection's transaction space. Another would be to use xp_cmdshell to fire up OSQL.EXE and run the query on a separate connection (and in a separate process). Where there's a will, there's a kludge . The best strategy of all, though, is to find out what's causing such a serious error and remedy it.

Problems That Seem Like Quirks but Aren't

Because @@ERROR is reset each time a statement executes successfully, processing it after an error can be tricky. For example, consider the code in Listing 7-7:

Listing 7-7 @@ERROR is easily reset.
 CREATE PROC ListTable AS SELECT * FROM MyTable GO EXEC ListTable IF @@ERROR<>0 BEGIN   PRINT @@ERROR   PRINT 'An error occurred, attempting to create the table'   CREATE TABLE MyTable   (c1 int)   INSERT MyTable DEFAULT VALUES   SELECT * FROM MyTable   DROP TABLE MyTable END Server: Msg 208, Level 16, State 1, Procedure ListTable, Line 3 Invalid object name 'MyTable'. 0 An error occurred, attempting to create the table c1 ----------- NULL 

As you can see from the output, @@ERROR is reset by the IF test. Obviously, this isn't what we intended. What you have to do instead is save off the value of @@ERROR immediately after the operation that you wanted to watch for errors. This way, exactly when @@ERROR gets reset is of no importance. We don't care; we've already cached its value. Here's the code rewritten to properly handle @@ERROR (Listing 7-8):

Listing 7-8 Cache @@ERROR to avoid resetting it.
 CREATE PROC ListTable AS SELECT * FROM MyTable GO DECLARE @res int EXEC ListTable SET @res=@@ERROR IF @res<>0 BEGIN   PRINT @res   PRINT 'An error occurred, attempting to create the table'   CREATE TABLE MyTable   (c1 int)   INSERT MyTable DEFAULT VALUES   SELECT * FROM MyTable   DROP TABLE MyTable END 

Make a habit of caching and checking @@ERROR after significant code executes, especially after DML operations. The chief characteristic of robust code is thorough error checking, and until Transact-SQL supports structured exception handling, @@ERROR is here to stay.

@@ROWCOUNT

There are times when an error condition does not actually result in an error message or set an error code. One of these is when a data modification against a table unexpectedly fails to change any rows. You can determine the number of rows affected by the last DML statement (SELECT, INSERT, UPDATE, or DELETE) by checking @@ROWCOUNT. When @@ROWCOUNT indicates that no rows were affected, you can treat the condition just like any other error. Here's an example (Listing 7-9):

Listing 7-9 Check @@ROWCOUNT to detect subtle errors.
 CREATE PROC ListTable AS SELECT * FROM Northwind..Customers WHERE 1=0 -- Get no rows GO DECLARE @res int EXEC ListTable SET @res=@@ROWCOUNT IF @res=0 BEGIN   PRINT 'ListTable returned no rows' END 

Notice that I saved @@ROWCOUNT to a variable immediately after the EXEC. Like @@ERROR, @@ROWCOUNT can be easily reset, so it's important to cache it immediately after the operation you're tracking.

Errors and Transaction Management

Probably the biggest problem with the lack of airtight error-handling facilities in Transact-SQL is the possibility that an aborted batch may orphan a transaction. If a batch with an open transaction is forced to abort due to an error, the transaction will be orphaned, and any locks that it holds will block other users. For example, consider the code in Listing 7-10:

Listing 7-10 SQL Server detects orphaned transactions.
 CREATE PROC TestTran AS DECLARE @ERR int BEGIN TRAN SELECT * FROM MyTable SET @ERR=@@ERROR IF @ERR<>0 BEGIN   RAISERROR('Encountered an error, rolling back',16,10)   IF @@TRANCOUNT<>0 ROLLBACK   RETURN(@ERR) END COMMIT TRAN GO EXEC TestTran 

(Results)

 Server: Msg 208, Level 16, State 1, Procedure TestTran, Line 4 Invalid object name 'MyTable'. Server: Msg 266, Level 16, State 1, Procedure TestTran, Line 11 Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1. 

As we discovered earlier, error 208 aborts the current batch, so the error-handling code that rolls back the transaction is never reached (notice SQL Server's warning about this immediately after the error message). What can you do about this? One method of dealing with it is to use the techniques from earlier in the chapter that isolate the problematic code into a subroutine so that it doesn't interrupt the execution flow of the caller. The problem is: Sometimes you don't know exactly what the "problematic" code isit could be anything. A more foolproof method is to check the open transaction count on entrance to every routine that begins a transaction and issue a ROLLBACK if any transactions are open. Here's the procedure from Listing 7-10 modified to do this (Listing 7-11):

Listing 7-11 A technique for dealing with orphaned transactions.
 CREATE PROC TestTran AS  IF @@TRANCOUNT<>0 ROLLBACK -- Check for orphaned trans and rollback  DECLARE @ERR int BEGIN TRAN SELECT * FROM MyTable SET @ERR=@@ERROR IF @ERR<>0 BEGIN   RAISERROR('Encountered an error, rolling back',16,10)   IF @@TRANCOUNT<>0 ROLLBACK   RETURN(@ERR) END COMMIT TRAN GO EXEC TestTran 

As you can see, one line of code is all that it takes to deal with what could be a very serious problem. Of course, this approach relies on a procedure being called in order to roll back the orphaned transaction. Until it is, the transaction will remain active.

The ultimate solution is for your application to detect that an error has occurred and that it needs to roll back an open transaction. Barring that, the techniques presented here should help you work around some of the shortcomings in Transact-SQL's error-handling facilities.

SET XACT_ABORT

SET XACT_ABORT toggles whether a transaction is aborted when certain types of runtime errors occur. It's supposed to abort when any runtime error occurs; however, it's been my experience that this isn't always so. There are errors that it can't deal with, and hence, it's still possible to orphan a transaction with the option enabled.

The error that triggers the automatic rollback can be a system-generated error or a user error. It's very much like checking @@ERROR after every statement and rolling back the transaction if an error is detected . Here's an example of SET XACT_ABORT at work (Listing 7-12):

Listing 7-12 SET XACT_ABORT can help avoid orphaned transactions.
 CREATE PROC TestTran AS DECLARE @ERR int  SET XACT_ABORT ON  BEGIN TRAN DELETE Customers SET @ERR=@@ERROR IF @ERR<>0 BEGIN   RAISERROR('Encountered an error, rolling back',16,10)   IF @@TRANCOUNT<>0 ROLLBACK   RETURN(@ERR) END COMMIT TRAN GO EXEC TestTran GO SELECT @@TRANCOUNT 

(Results)

 Server: Msg 547, Level 16, State 1, Procedure TestTran, Line 5 DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Orders_Customers'. The conflict occurred in database 'Northwind', table 'Orders', column 'CustomerID'. ----------- 0 

The constraint violation causes the transaction to be aborted because SET XACT_ABORT is enabled. This is an easy way to roll back in response to a wide variety of different types of errors. However, not all errors can be trapped by SET XACT_ABORT. Some types of errors do not trigger a transaction rollbackeven though you may need and expect them to. Here's an example (Listing 7-13):

Listing 7-13 Some errors do not trigger XACT_ABORT's rollback.
 CREATE PROC TestTran AS DECLARE @ERR int  SET XACT_ABORT ON  BEGIN TRAN  SELECT * FROM NoTable  SET @ERR=@@ERROR IF @ERR<>0 BEGIN   RAISERROR('Encountered an error, rolling back',16,10)   IF @@TRANCOUNT<>0 ROLLBACK   RETURN(@ERR) END COMMIT TRAN GO EXEC TestTran GO SELECT @@TRANCOUNT 

(Results)

 Server: Msg 208, Level 16, State 1, Procedure TestTran, Line 5 Invalid object name 'NoTable'. Server: Msg 266, Level 16, State 1, Procedure TestTran, Line 12 Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1. ----------- 1 

Once again, error 208 proves fatal to the batch and orphans the transaction. Even though, logically, we'd expect XACT_ABORT to kick in and clear out the orphaned transaction, this is not what happens. Generally, SET XACT_ABORT cannot "catch" compile-time errors. This is probably a good place to make use of the workaround shown in Listing 7-11. If each stored procedure proactively rolls back orphaned transactions, hopefully the overall effect of the orphaned transactions on concurrency will be minimal. Until Transact-SQL has support for structured language exceptions, you'll have to make use of some of the workarounds demonstrated in this chapter to properly handle errors and avoid wasting or orphaning resources.

for RuBoard


The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
ISBN: 201700468
EAN: N/A
Year: 2005
Pages: 223

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net