Guru's Guide to SQL Server™ Stored Procedures, XML, and HTML, The

Handling Errors

Of all the things that frustrate and bewilder Transact-SQL developers, error handling is probably at the top of the list. There are several reasons for this. Chief among them is that Transact-SQL's error handling simply doesn't work properly in some circumstances. It either doesn't work as documented or the way it works doesn't make any sense. Let's begin by talking about how error handling is supposed to work in Transact-SQL.

@@ERROR

When an error occurs, @@ERROR normally contains its error number. Your code should check @@ERROR after significant operations. Listing 7-1 is an example of an error and the type of T-SQL code that's needed to handle it:

Listing 7-1. Sample error handling code.

USE Northwind DECLARE @c int SELECT ...