Error handling was a pretty tough job in the earlier versions of SQL Server. Developers had to perform lot of conditional checks for the error code returned after each INSERT, UPDATE or DELETE operations. Developers must check the @@ERROR attribute each time they might see the possibility of an error based operations. Error messages could be generated either from the SQL Server or thrown explicitly by the user. Let us first see how developers usually perform the error handling in SQL Server 2000. We shall have a stored procedure for our demonstration. Let us use AdventureWorks database for our demonstration purpose.

Listing 2

CREATE PROCEDURE ErrorHandlerDemo
AS
BEGIN
BEGIN TRANSACTION
DECLARE @EmpID AS BIGINT
INSERT INTO [HumanResources].[Employee] ([NationalIDNumber], [ContactID], 
[LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], 
[HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], 
[rowguid], [ModifiedDate]) 
VALUES ('1441784507', 120439, 'adventure-works\guy43', 13456, 'Production 
Technician - WC60', '19720515', 'M', 'M', '19960731', 0, 21, 30, 1, 
'AAE1D04A-C237-4974-B4D5-735247737718', '20040731')
IF @@ERROR != 0 GOTO ERROR_HANDLER
SET @EmpID = @@IDENTITY
INSERT INTO [HumanResources].[EmployeeAddress] ([EmployeeID], [AddressID], 
[rowguid], [ModifiedDate]) 
VALUES (@EmpID, 61, '77253AEF-8883-4E76-97AA-7B7DAC21A2CD', 
'20041013 11:15:06.967')
IF @@ERROR != 0 GOTO ERROR_HANDLER
COMMIT TRANSACTION
ERROR_HANDLER:
    ROLLBACK TRANSACTION
    RETURN @@ERROR
END
GO

When we try to execute this procedure we get the output as:

Listing 3

Msg 547, Level 16, State 0, Procedure ErrorHandlerDemo, Line 11

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Employee_Contact_ContactID." The conflict occurred in database "AdventureWorks" table "Person.Contact" column "ContactID."

The statement has been terminated.

As you could see, the error message has Msg, Level of severity, State and Line. The “Msg” holds the error number generated for the message, in this case 547. All the error messages are defined in the table called sys.messages. If you are going for custom error handling then you can utilize the sp_addmessage system procedure to implement new error messages.

Next, you have the severity “Level” of the message. Severity codes lie in the range of 0 to 25. Any error message above 20 will terminate the connection. Error messages from 17 to 20 specify a resource problem, from 11 to 16 specify error messages in the T-SQL scripts, and error messages below 11 specify warnings.

Next we have the "State" of the error message. This is an arbitrary integer range falling in between 0 to 127. This provides information on the source that has issued the error message. However, there is not much documentation disclosed by Microsoft on this.

Next is the "Line" number which tells us the line where the error has occurred in the procedure or T-SQL batch. And the last one is the message itself.

The understanding and implementation of the error handling in the earlier versions of SQL Server 2005 was fair enough, but was with a lot of housekeeping activity.  SQL Server 2005 provides flexible means to handle error handling mechanism by using the TRY and CATCH blocks.

The syntax looks like:

Listing 4

BEGIN
    TRY
    BEGIN TRANSACTION 
    …..
    --perform insert update and delete statements
    …..
    …..
    COMMIT TRANSACTION 
END TRY
BEGIN
--Start
    CATCH
    ROLLBACK TRANSACTION 
    PRINT ERROR_NUMBER()
    PRINT ERROR_SEVERITY()
    PRINT ERROR_STATE()
    PRINT ERROR_PROCEDURE()
    PRINT ERROR_LINE()
    PRINT ERROR_MESSAGE()
--End
END CATCH

So as you could see from the above script, the error handling mechanism is simplified. When an error occurs, the statement is terminated from the current execution point and it enters the catch block. The functions following the PRINT statements are built-in functions that provide information on the error message. You could also embed the code from start to end in a stored procedure and call it wherever you need. You can also log the error messages in a table for debugging purpose. AdventureWorks database handles error handling in similar manner; you could find procedures uspLogError and uspPrintError which do the job.

You could also use the RAISERROR to define you own custom error messages. The RAISERROR may take a system error code or a user defined error code which would be eventually fired by the server to the connected application or within the try..catch block. The example of using RAISERROR is:

Listing 5

RAISERROR (
-- Message text.
'A serious error has terminated the program. Error message is %s, Error code is %d.', 
-- Severity
     10, 
-- State
     1, 
-- Argument 1.
     '...statement conflicted...', 
-- Argument 2.
      52000); 

The output of this RAISERROR would look like:

A serious error has terminated the program. Error message is ...statement conflicted..., Error code is 52000.

Next time when you are working with T-SQL code, you need not really worry about implementing numerous checks for errors. The TRY..CATCH feature helps with a better approach for error handling which would minimize the size of your code, thus improving readability.