In a recent article, I demonstrated how to catch errors in
SQL Server 2000. The most significant aspect of error handling in SQL
Server 2000 is the @@ERROR variable. You must query the value of this variable
after every statement execution to ensure no error has occurred that would
require the transaction to be rolled back. This approach is cumbersome, and
more importantly error prone. In addition, this form of error handling in SQL
Server 2000 is limited to only working for certain types of errors. Errors that
would abort the transaction or batch cannot be handled, and detailed error
information isn’t available.

TRY…CATCH

SQL Server 2005 provides the TRY…CATCH construct, which is
already present in many modern iterative programming languages, such as Java
and C#. This construct gives you access to much more detailed error information
through a series of new functions available in the CATCH construct, which
include the following:

  • ERROR_NUMBER: returns the error
    number, and is the same value of @@ERROR.
  • ERROR_SEVERITY: returns the
    severity level of the error that invoked the CATCH block.
  • ERROR_STATE: returns the state
    number of the error.
  • ERROR_LINE: returns the line
    number where the error occurred.
  • ERROR_PROCEDURE: returns the name
    of the stored procedure or trigger for which the error occurred.
  • ERROR_MESSAGE: returns the full
    message text of the error.

You can use these functions anywhere inside a CATCH block,
and they will return information regarding the error that has occurred. These
functions will return the value null outside of the CATCH block.

Weekly SQL tips in your inbox

TechRepublic’s free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.

Automatically sign up today!

Handling deadlock errors

Let’s take a look at an example of how we can use the new
error handling capabilities in SQL Server 2005 to handle deadlock situations,
which are virtually impossible to handle at the
database level in SQL Server 2000.

A deadlock occurs when there is competition for resources on
your computer. This situation is not isolated solely for database management
systems, and occurs for your operation system, or any other system for which
resource contention occurs. A deadlock occurs when one process acquires
specific locks on a resource and needs additional resources to complete its
task. If another process holds locks on the resources needed by the first
process and additionally needs the resources the first process has acquired, a
stalemate occurs. Neither process will release its resources, which means that
neither process will be able to complete its task.

The good news is that SQL Server has algorithms in place
that will arbitrarily pick a loser in this situation, which frees up resources
so one process can complete its work. This means that the process that was
terminated must be retried. In SQL Server 2000 and earlier, the best you could
do to remedy this situation was to code specifically for deadlocks in your
business layer and retry your transactions if you detect a deadlock. If you
notice trends over time with deadlocks situations, you could include logic in
your stored procedures that would set the deadlock priority. This approach
allows you to pick the loser in deadlock situations, but it leaves you without
any means to retry the terminated processes.

With SQL Server 2005, you can catch the errors at the data
layer, so your business layer developer doesn’t have to worry about retrying
the transaction. If you are able to catch a deadlock error, you will need to
retry the statement (probably after a period of time has elapsed so the
resources that were acquired can be released).

To demonstrate how these new capabilities work, check out Listing A. The code creates the table I
will use to log errors that occur. I want to record all of the information from
the error-handling functions, along with the date the error occurred and the
database that the error occurred in.

I’ll use the code in
Listing B to log any errors that occur
in our procedures. Notice that you do not have to pass any parameters to the
procedure, and that the procedure has access to the error-handling functions
described above. This is because you will call this procedure in the execution
context of the CATCH block. You can reference these functions at any point in
the CATCH block—even when calling other procedures.

Listing C is
designed to check specifically for the deadlock error number, which is 1205. If
a deadlock occurs when the table FicticiousTable1 is updated, the statement is
tried again up to three times. If after three times, the update does not
succeed, the attempts to update the table are aborted.

Benefits of error handling in SQL Server
2005

SQL Server 2005 offers a more
robust set of tools for handling errors than in previous versions of SQL
Server. Deadlocks, which are virtually impossible to handle at the database
level in SQL Server 2000, can now be handled with ease. By taking advantage of
these new features, you can focus more on IT business strategy development and
less on what needs to happen when errors occur.

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.