Chapter 6. Error handling in SQL Server and applications

 

Bill Graziano

Prior to SQL Server 2005, error handling was limited to testing @@ERROR after each statement. This required users to write lots of similar code to do error handling. The introduction of TRY...CATCH blocks in Transact-SQL (T-SQL) gives the developer more options to detect and handle errors. It is now possible to completely consume errors on the server. Or if you’d like, you can return specific errors back to the client.

The .NET development languages also have a series of classes for SQL Server error handling. These can be used to process errors and capture informational messages from SQL Server. The combination of TRY...CATCH on the server and the SQL Server–specific error handling on the client give developers many options to capture errors and handle them appropriately.

Handling errors inside SQL Server

Consider the following T-SQL statements that generate an error:

Listing 1. Error sent to SQL Server Management Studio

This returns the following result to SQL Server Management Studio:

First
-----------
1

Second
-----------
Msg 8134, Level 16, State 1, Line 4
Divide by zero error encountered.
Third
-----------
3

The error message from the second SELECT statement is displayed and then the third SELECT statement executes. This error would’ve been returned to a client application.

Handling SQL Server errors on the client

Summary

About the author