The support for exception handling in SQL Server 2000 had several limitations. It was nonstructured, it was awkward, and it wasn’t capable of trapping all errors.

The main tool it provided you for exception handling was the @@error function, which returned an integer representing the way the last statement terminated (with 0 indicating success and a non-zero error code indicating lack of success). You had to assign the value of @@error to your own local variable immediately after each suspect statement. Otherwise, you’d lose that value, as it would be overridden by the statement that followed the suspect one. Then you had to switch between the possible error codes and determine a course of action. You had to either provide exception-handling code after each suspect statement or label an exception handling block and pass control to it using a GOTO command. Either way, the result was typically nonstructured code that was very hard to maintain.

Even worse, there were many errors that you simply couldn’t trap, even ones that were not considered severe. Some errors simply terminated your batch and didn’t give the exception-handling code a chance to run, so you had to deal with those at the caller. Examples of errors that terminated your batch include conversion errors and deadlock.

There’s a simple test you can perform to check whether an error terminates the batch or not. Simply print something immediately after the statement that generates the error. Then check whether output was generated. For example, the following code does invoke the PRINT statement after a divide-by-zero error is generated:

SELECT 1/0;
PRINT 'Trappable error.';

This tells you that a divide-by-zero error is trappable. On the other hand, the following code doesn’t invoke the PRINT statement after the conversion error is generated, telling you that a conversion error terminates the batch:

SELECT 'A' + 1;
PRINT 'Trappable error.';

Similarly, resolution or compilation errors terminate the batch and are not trappable–for example, referring to a nonexisting object:

SELECT * FROM NonExistingObject;
PRINT 'Trappable error.';

Another tricky part about exception handling in SQL Server 2000 was dealing with volatile functions, such as @@error and @@rowcount, that change their values after each statement that is run. The @@error function gives you an integer representing the way the last statement terminated. The @@rowcount function gives you the number of rows affected by the last statement. In many cases, you needed both for exception handling. However, if you used separate SET statements to assign the function result values to your own variables (as shown in the following code), the second SET statement would not assign the correct value to @rc.

<suspect_statement>
SET @err = @@error;
SET @rc = @@rowcount;

What you end up getting in @rc is the number of rows affected by the first assignment (namely 0), instead of the number of rows affected by the suspect statement. To trap both function result values, you could use a single assignment SELECT statement:

SELECT @err = @@error, @rc = @@rowcount;

Here’s a small example of how to trap both values immediately after a suspect query:

SET NOCOUNT ON;
USE Northwind;
GO

DECLARE @custid AS NCHAR(5), @err AS INT, @rc AS INT;
SET @custid = N'ALFKI';

SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE CustomerID = @custid;

SELECT @err = @@error, @rc = @@rowcount;
-- exception handling code goes here
SELECT @err AS error_number, @rc AS row_count;

If the suspect code appeared within a stored procedure and you wanted the caller of the procedure to deal with the error, you had to pass the @@error and @@rowcount values through the return status/output parameters of the procedure. For example, the following usp_GetCustomerOrders procedure returns orders for a given customer ID and date range:

CREATE PROC dbo.usp_GetCustomerOrders
  @custid   AS VARCHAR(5),
  @fromdate AS DATETIME = '19000101',
  @todate   AS DATETIME = '99991231 23:59:59.997',
  @numrows  AS INT OUTPUT
AS

-- Input validation goes here

DECLARE @err AS INT;

SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE CustomerID = @custid
  AND OrderDate >= @fromdate
  AND OrderDate < @todate;

SELECT @numrows = @@rowcount, @err = @@error;

RETURN @err;
GO

The procedure returns the @@error value as the return status, and it returns the @@rowcount value through an output parameter called @numrows. Example 10-1 has code that invokes the stored procedure and applies exception handling.

SET LOCK_TIMEOUT 5000;
DECLARE @err AS INT, @rc AS INT;

EXEC @err = dbo.usp_GetCustomerOrders
  @custid   = N'ALFKI', -- Also try with N'ABCDE'
  @fromdate = '19970101',
  @todate   = '19980101',
  @numrows  = @rc OUTPUT;

SELECT @err AS error_number, @rc AS row_count;

IF @err = 0 AND @rc > 0 BEGIN
  PRINT 'Processing Successful';
  RETURN;
END

IF @err = 0 AND @rc = 0 BEGIN
  PRINT 'No rows were selected.';
  RETURN;
END

IF @err = 1222
BEGIN
  PRINT 'Handling lock time out expired error.';
  RETURN;
END
-- other errors
-- IF @err = ...
BEGIN
  PRINT 'Unhandled error detected.';
  RETURN;
END

If the query completes with no errors and returns rows, the first IF statement will print the message ′Processing Successful′ and exit. If the query completes with no errors and returns no rows, the second IF statement will print the message ′No rows were selected.′ and exit. The PRINT statement represents the section of code where you might want to take care of a no-rows situation. If an error took place, the code switches between the possible errors using a series of IF statements. To test the code, run it for the first time. You will get back three orders for the requested customer in the requested date range, and you will identify a successful run–that is, @@error was 0 and @@rowcount was 3 (>0).

Next open a new connection and run the following code to lock the Orders table:

BEGIN TRAN
  SELECT * FROM dbo.Orders WITH (TABLOCKX);

Now go back to the original connection, and run the code in Example 10-1 again. After about five seconds, a lock timeout expiration error will be generated, and you will get the following output:

Msg 1222, Level 16, State 56, Procedure usp_GetCustomerOrders, Line 13
Lock request time out period exceeded.
error_number row_count
------------ -----------
1222         0

Handling lock time out expired error.

When you’re done, issue a rollback in the second connection to terminate the transaction:

ROLLBACK

There are other limitations to exception handling in SQL Server 2000. In addition to the error number you get from the @@error function, there’s no other information available about the error–not the message, not the severity, and not the state.

More Info

You can find interesting information about exception handling in SQL Server at Erland Sommarskog’s Web site: www.sommarskog.se. Erland is a very active SQL Server MVP, and the subject of exception handling is very close to his heart.