Error handling

The main tool used for error handling in SQL Server is the TRY…CATCH construct. SQL Server also provides functions we can use to get information about the error.

We place the usual T-SQL code in a TRY block (between the BEGIN TRY and END TRY keywords) and place all the error-handling code in the adjacent CATCH block (between the BEGIN CATCH and END CATCH keywords). The CATCH block is simply skipped if the TRY BLOCK has no error. If the TRY block has an error, control is passed to the corresponding CATCH block. If the TRY…CATCH block captures and handles and error, there is no error as far as the caller is concerned.

The following code demonstrates a case where there is no error in the TRY block:

(Include code, explanation, and results from page 412)

Since the code in the TRY block completed successfully, the CATCH block was skipped. The following code has a divide by zero error:

(Include code, explanation, and results from page 412)

When the divide by zero error happened in the first PRINT statement in the TRY block, control was passed to the corresponding CATCH block. The second PRINT statement in the TRY block is not executed, which is why the query returned Error.

As previously mentioned, SQL Server provides functions that gives us information about the error. The ERROR_NUMBER function returns an integer with the number of the error. The CATCH block includes flow code that inspects the error number to determine what course of action to take. The ERROR_MESSAGE function returns error-message text. For a list of error numbers and messages, we can query the sys.messages catalog view.

The ERROR_SEVERITY and ERROR_STATE functions returns the error severity and state. The ERROR_LINE function returns the line number in the code where the error happened. The ERROR_PROCEDURE function returns the name of the procedure in which the error happened return the name of the procedure in which the error happened and returns NULL if the error did not happen within a procedure.

The following code creates a demo table called dbo.Employees in the current database:

(Include code, explanation, and results from page 413)

The following code inserts a new row into dbo.Employees in a TRY block, and if an error occurs, shows how to identify the error by inspecting the ERROR_NUMBER function in the CATCH block. The code uses flow control to identify and handle errors we want to deal with in the CATCH block, and it re-throws the error otherwise. The code also prints the values of the other error functions simply to show what information is available to us when an error occurs.

(Include code, explanation, and results from page 413)

When we run this code for the first time, a new row is inserted into dbo.Employees successfully, and therefore the CATCH block is skipped. When we run the same code a second time, the INSERT statement fails, control is passed to the CATCH block, and a primary-key-violation error is identified. We get the following output:

(Include results from page 414)

To see other errors, we can run the code with the values 0, ‘A’, and NULL as the employee ID.

While in this demonstration we used PRINT statements as the actions when an error was identified, error handling usually involves more than just printing a message indicating that the error was identified.

The following code is a stored procedure that encapsulates reusable error-handling code:

(Include code, explanation, and results from page 414)

In our CATCH block, we check whether the error number one of those we want to deal with locally. If it is, we simply execute the stored procedure; otherwise, we re-throw the error:

(Include code, explanation, and results from page 415)