Transaction design and error handling in SQL Server 2000 is no easy task. Tim Chapman provides insight into designing transactions and offers a few tips to help you develop custom error handling routines for your applications.
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.
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'
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.
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()),
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
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
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 firstname.lastname@example.org.