Most iterative language compilers have built-in
error handling routines (e.g., TRY…CATCH statements) that developers can
leverage when designing their code. Although SQL Server 2000 developers don’t
enjoy the luxury that iterative language developers do when it comes to
built-in tools, they can use the @@ERROR system variable to design their own
effective error-handling tools.

Introducing transactions

In order to grasp how error handling works in SQL Server
2000, you must first understand the concept of a database transaction. In
database terms, a transaction is a series of statements that occur as a single
unit of work. To illustrate, suppose you have three statements that you need to
execute. The transaction can be designed in such a way so that all three statements
occur successfully, or none of them occur at all.

When data manipulation operations are performed in SQL
Server, the operation takes place in buffer memory and not immediately to the
physical table. Later, when the CHECKPOINT process is run by SQL Server, the
committed changes are written to disk. This means that when transactions are
occurring, the changes are not made to disk during the transaction, and are
never written to disk until committed. Long-running transactions require more
processing memory and require that the database hold locks for a longer period
of time. Thus, you must be careful when designing long running transactions in
a production environment.

Here’s a good example of how using transactions is useful.
Withdrawing money from an ATM requires a series of steps which include entering
a PIN number, selecting an account type, and entering the amount of funds you
wish to withdraw. If you try to withdraw $50 from the ATM and the machine fails
thereafter, you do not want to be charged the $50 without receiving the money.
Transactions can be used to ensure this consistency.

The @@ERROR variable

Successful error handling in SQL Server 2000 requires
consistently checking the value of the @@ERROR system variable. @@ERROR is a
variable updated by the SQL Server database engine after each statement is executed on the server for the given connection. This variable contains the
corresponding error number, if applicable. You can find a listing of these error numbers in the sysmessages table in the master database. The details of this table are listed on Microsoft’s site.

Here’s an example of how the @@ERROR variable works:

PRINT 'Taking a look at @@ERROR'

PRINT @@ERROR

In these instructions, we are printing out a string to the
screen and printing the value of the @@ERROR variable. Because no error is
returned from printing out to the screen, the value @@ERROR contains is 0.

PRINT 1/0

PRINT @@ERROR

In this example, we generate a division by zero error, which
means that the @@ERROR variable will contain 8134, which is the error number
that Microsoft assigns for this type of error. For most error handling purposes,
you will only be concerned if the value of @@ERROR is non-zero, which will
indicate that an error occurred. It is a good idea to keep track of the error
numbers when recording the errors as they will come in handy during the
debugging process.

Error handling at work

Here’s a good example of how you can use error handling in
stored procedures. The goal of the sample script is to execute a stored
procedure that will declare a transaction and insert a record into a table.
Because this is for explanation purposes only, we will design the procedure in
such a way as to let us tell it whether to commit or roll back the transaction.

Execute the following statement to create the table that we will use for our example:

CREATE TABLE Transactions

(
      TranID SMALLINT IDENTITY(1,1) PRIMARY KEY,
      EntryDate SMALLDATETIME DEFAULT(GETDATE()),
      ParamValue CHAR(1), 
      ThrowError BIT

)

The two fields of value in the script are ParamValue and ThrowError. These
fields will correspond to the input parameters of the procedure we will create,
and we will use them in our logic for committing transactions.

Once our table is in place to keep track of our
transactions, we are ready to create our procedure. The procedure will have a
parameter used simply to record a character value and a parameter, which will
give us the ability to throw an error in the procedure. Run the statement in
Listing A to create the procedure.

This simple stored procedure exhibits the characteristics we
need for effective error handling. First, a transaction is explicitly declared.
After a record is inserted into the Transaction table, we check the value of
the @ThrowError parameter. This parameter indicates
whether to throw an error, and uses the RAISERROR function to throw the custom
error. When the RAISERROR function is called, the value of the @@ERROR variable
is populated with the error number that we provide.

If an error occurs in the stored procedure, we will roll back
the transaction. Rolling back the transactions means that the record we
attempted to insert into the Transactions table will be removed as if it never
occurred. The state of the database will be exactly how it was before the
transaction began.

In this example, you will also notice the use of the GOTO
statement and the label ErrorHandler. GOTO statements
are typically considered a bad programming practice in iterative programming
languages, but they are very useful when handling errors in SQL Server 2000.
Don’t be afraid to use the GOTO statement to handle errors.

This procedure call will throw an error and the record will
not be inserted in the Transactions table:

DECLARE @ReturnCode INT

EXECUTE @ReturnCode = usp_TestTransaction @ParamValue = 'E', @ThrowError = 1

PRINT @ReturnCode

This procedure call will not throw an error, and the
inserted record will be committed to the Transactions table:

DECLARE @ReturnCode INT

EXECUTE @ReturnCode = usp_TestTransaction @ParamValue = 'S', @ThrowError = 0

PRINT @ReturnCode

These procedure calls make use of a Return parameter, which
indicates the success or failure of a stored procedure. It is a good
programming practice to explicitly set the Return parameter in your code to
indicate success or failure of the procedure; this allows you to know when your
stored procedure has failed so you can take the necessary steps to handle the
failure. For example, you can nest procedure calls and transactions. Your
application could potentially declare a transaction, call a stored procedure,
and (depending on the success or failure of the stored procedure) commit or roll
back the outside transaction.

Looking to the future

Careful transaction design and consistently checking the
value of the @@ERROR variable is the key to effective error handling in SQL
Server 2000. In a future article, I’ll show you how to use the new error
handling capabilities in SQL Server 2005, which make use of TRY…CATCH
statements.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.