Handle errors in the data tier with SQL Server

by Tony Patton | Jun 06, 2006 1:05:00 PM

Tags: Databases, .NET, Tony Patton, server, RAISERROR statement, Microsoft SQL Server Transact-SQL, database, Microsoft SQL Server, .NET Newsletter

Takeaway: Handling exceptions in your .NET application code is a simple and straightforward process with try/catch code blocks, but you can monitor exceptions on the database tier as well. We examine error handling in your database code with SQL Server and T-SQL.

Developers work hard to graciously handle exceptions so users don't have to worry about the arcane system error messages. For this reason, exception handling is a standard part of every .NET application. The try/catch block allows you to catch exceptions and control application execution from that point. Many errors occur during database interaction, but a lot of developers are unaware of handling errors within the database tier. This week we examine error handling in your database code with SQL Server and T-SQL.

Working with errors in T-SQL

SQL Server's SQL dialect T-SQL allows you to easily handle non-fatal errors that may occur in a stored procedure, function, etc., but not all errors are easily handled. In fact, there are fatal and non-fatal errors. Fatal errors stop execution while non-fatal errors do not. It isn't well-documented what is fatal and what is non-fatal, but your application code can always handle fatal database errors with try/catch blocks. For all other errors, you can use the following techniques.

Transactions

You should use transactions in your database code to ensure all changes are made if and only if everything is okay. SQL Server online help describes transactions as a logical unit of work made up of a series of statements (selects, inserts, updates, or deletes). If no errors are encountered during a transaction, all of the modifications in the transaction become a permanent part of the database. If errors are encountered, none of the modifications are made to the database.

Transactions are contained between BEGIN TRANSACTION and END TRANSACTION statements. The ROLLBACK TRANSACTION statement allows all changes to be cancelled, thus no changes are made. Changes are made permanent with the COMMIT TRANSACTION statement. Now, let's turn our attention to working with errors in your T-SQL.

@@Error

The @@Error function allows you to implement T-SQL error handling. It returns the error code returned by the system. A zero is returned if no error occurs. The @@Error function must be called immediately after a statement because it is cleared after each T-SQL statement.

RAISERROR

The RAISERROR statement allows you to generate a custom error message or use an existing message located in the sysmessages table. You can view its syntax online, but its most basic format includes the message (for custom messages) or message id (for existing messages) along with its severity and state. State is not used by SQL Server, so pass it an arbitrary numeric. The severity signifies the error's severity level with 0 – 18 available to the user and 19 – 25 reserved for administrators.

In Listing A, the sample stored procedure utilizes these features to update an individual record in the sample Northwind database. It sets the phone number column to a value passed into the procedure only if no errors occur. It uses a return value parameter to return a negative one if problems occur or a positive one if everything executes without problems.

Working with stored procedure return values

With the stored procedure returning values, we can use it in our .NET code. The SqlCommand object allows you to easily add parameters to pass into the procedure as well as stored return values. The parameter's Direction property is used to receive return values from a stored procedure call. It has two property values: InputOutput and Output. In our example, we'll use Output to receive the status value.

The code is a simple ASP.NET page that passes a new value for a specific record in the Northwind's database customers table. The id value is actually stored in a hidden field—it could easily be passed to the form, but the field works for the demonstration. The value entered in a text field is used to update the phone field in the database table.

The parameters are added to the SqlCommand object (they must exactly match the parameter values of the stored procedure). The command is executed via the ExecuteNonQuery method of the SqlCommand object. Once it has been executed, the return value may be retrieved via the parameters.

The code checks the return value (-1 indicates a problem) and a message is displayed in a Label control. In addition, a try/catch block is used to catch any fatal errors that may occur when interacting with the database. View Listing B. (Listing C contains the equivalent VB.NET code.)

Cover all your bases

Handling exceptions in your .NET application code is a simple and straightforward process with try/catch code blocks, but you can monitor exceptions on the database tier as well. SQL Server's T-SQL language provides everything you need to keep up with code execution.

Miss a column?

Check out the .NET Archive, and catch up on the most recent editions of Tony Patton's column.

Tony Patton began his professional career as an application developer earning Java, VB, Lotus, and XML certifications to bolster his knowledge.

Print/View all Posts Comments on this article

Listing A saa@... | 10/31/07

Wrong place nt Tony Hopkinson | 10/31/07

RE: Handle errors in the data tier with SQL Server Denise T | 10/31/07

RE: Handle errors in the data tier with SQL Server TWB3 | 10/31/07

Why return -1? yaoliu68@... | 10/31/07

No, it's not an exception Tony Hopkinson | 10/31/07

Neither simple nor straight forward Tony Hopkinson | 10/31/07