Deep understanding of error handling in SQL Server

Most iterative language compilers have built-in error handlers (for example, TRY ...). Catch statements, developers can use them when designing code. Although SQL Server 2000 developers cannot take advantage of built-in tools like the developers of iterative languages, they can design their own effective error-handling tools with the system variable @ @ERROR.

Introducing a transaction

In order to master how error handling works in SQL Server 2000, you must first understand the concept of database transactions. In database terminology, a transaction refers to a series of statements that appear as a separate unit of work. For example, if you have three statements to execute, the transaction can be designed to do so, or all three statements will succeed, or none will occur.

When performing a data processing operation in SQL Server, the operation occurs in the buffer memory and is not immediately written to the actual table. Then, when SQL Server runs the checkpoint process, the changes that have occurred are written to disk. This means that, in the event of a transaction, the change is not written to the disk until it is committed before it is written. A long-running transaction requires more processing storage space and requires the database to remain locked for a longer period of time. So when you design a long-running transaction in a working environment, be careful.

The following is a good example of the benefits of leveraging transactions. Taking money from ATM requires the following steps: Enter a PIN number, select an account type, and enter the amount of money you want to withdraw. If you try to withdraw 50 dollars from an ATM and then fail, you won't be tempted to deduct 50 dollars without getting paid. Transactions can be used to ensure this consistency.

Successful error handling in SQL Server 2000 requires consistently checking the value of the system variable @ @ERROR. @ @ERROR is a variable that is updated by the SQL Server database engine after all statements are executed on the server under a given connection. This variable includes the corresponding error number, if any. You can find a list of these error numbers in the sysmessages table in the main database. Details on this form have been listed on the Microsoft website.

Here is an example of how the variable @ @ERROR works:

PRINT 'Taking a look at @@ERROR'
PRINT @@ERROR

In these instructions, we will display a string on the screen and output the value of the variable @ @ERROR. The @ @ERROR value is 0 because there is no error in the output on the screen.

PRINT 1/0
PRINT @@ERROR

In the example above, we have a 0 divisor error, so the variable @ @ERROR will include the number 8134, which is the error code that Microsoft assigns to this error. For most error handling, you are only concerned when the @ @ERROR value is not 0 o'clock, which indicates an error occurred. It is a good idea to pay attention to each error number when recording errors, because it is convenient to call them during debugging.

Error handling in the work

Here is an example of how to use error handling in stored procedures. The purpose of the sample script is to execute a stored procedure, define a transaction, and then insert a record in a table. Because it's just for illustration purposes, we design the program in the following way so that we know whether to commit or rerun the transaction.

Execute the following statement to create the table we are going to use in the example:

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