Error handling in SQL Server breaks down into two very distinct situations: you’re handling errors because you’re in SQL Server 2005 or you’re not handling errors because you’re in SQL Server 2000. What’s worse, not all errors in SQL Server, either version, can be handled. I’ll specify where these types of errors come up in each version.

The different types of error handling will be addressed in two different sections. ‘ll be using two different databases for the scripts as well, [pubs] for SQL Server 2000 and [AdventureWorks] for SQL Server 2005.

I’ve broken down the scripts and descriptions into sections. Here is a Table of Contents to allow you to quickly move to the piece of code you’re interested in. Each piece of code will lead with the server version on which it is being run. In this way you can find the section and the code you want quickly and easily.

As always, the intent is that you load this workbench into Query Analyser or Management Studio and try it out for yourself! The workbench script is available in the downloads at the bottom of the article.

• GENERATING AN ERROR
• SEVERITY AND EXCEPTION TYPE
• TRAP AN ERROR
• USING RAISERROR
• RETURNING ERROR CODES FROM STORED PROCEDURES
• TRANSACTIONS AND ERROR TRAPPING
• EXTENDED 2005 ERROR TRAPPING

SQL Server 2000 – GENERATING AN ERROR

SQL Server 2005 – GENERATING AN ERROR

SQL Server 2000 AND 2005 – ERROR SEVERITY AND EXCEPTION TYPE

The error message provides several pieces of information:

Msg

A message number identifies the type fo error. Can up to the value of 50000. From that point forward custom user defined error messages can be defined.

Level

The severity level of the error.

• 10 and lower are informational.
• 11-16 are errors in code or programming, like the error above.
• Errors 17-25 are resource or hardware errors.
• Any error with a severity of 20 or higher will terminate the connection (if not the server).

Line

Defines which line number the error occurred on and can come in extremely handy when troubleshooting large scripts or stored procedures.

Message Text

The informational message returned by SQL Server.

Error messages are defined and stored in the system table sysmessages.

SQL Server 2000 – CATCH AN ERROR

SQL Server 2000 does not allow us to stop this error being returned, but we can try to deal with it in some fashion. The core method for determining if a statement has an error in SQL Server 2000 is the @@ERROR value. When a statement completes, this value is set.

If the value equals zero(0), no error occured. Any other value was the result of an error.

The following TSQL will result in the statement ‘A constraint error has occurred’ being printed,as well as the error.

@@ERROR is reset by each and every statement as it occurs. This means that if we use the exact same code as above, but check the @@ERROR function a second time, it will be different.

You will see the error number as returned by the @@ERROR statement as being zero(0), despite the fact that we just had a clearly defined error.

The problem is, while the UPDATE statement did in fact error out, the IF statement executed flawlessly and @@ERROR is reset after each and every statement in SQL Server.

In order to catch and keep these errors, you need to capture the @@ERROR value after each execution.

Now we can capture the error number and refer to it as often as needed within the code.

SQL Server 2005 – CATCH AN ERROR

While @@ERROR is still available in SQL Server 2005, a new syntax has been added to the T-SQL language, as implemented by Microsoft: TRY... CATCH.

This allows us to finally begin to perform real error trapping.

While there is an error encountered in the code, none is returned to the calling function. In fact, all that will happen in this case is the string 'Error Handled' is returned to the client.

We have actually performed the function of error trapping within TSQL.

There are a number of issues around the use of TRY...CATCH that have to be dealt with, which we will cover later. For example, simply having a TRY...CATCH statement is not enough.

Consider this example:

The second error is handled, but the first one is not and we would see this error returned to client application:

To eliminate this problem place multiple statements within the TRY statement.

SQL Server 2000 – USING RAISERROR

The RAISERROR function is a mechanism for returning to calling applications errors with your own message. It can use system error messages or custom error messages. The basic syntax is easy:

To execute RAISERROR you’ll either generate a string, up to 400 characters long, for the message, or you’ll access a message by message id from the master.dbo.sysmessages table.

You also choose the severity of the error raised. Severity levels used in RAISERROR will behave exactly as if the engine itself had generated the error. This means that a SEVERITY of 20 or above will terminate the connection. The last number is an arbitrary value that has to be between 1 and 127.

You can format the message to use variables. This makes it more useful for communicating errors:

You can use a variety of different variables. You simply have to declare them by data type and remember that, even with variables, you have a 400 character limit. You also have some formatting options.

A few notes about severity and status. Status can be any number up to 127 and you can make use of it on your client apps. Setting the Status to 127 will cause ISQL and OSQL to return the error number to the operating environment.

SQL SERVER 2005 – USING RAISERROR

The function of RAISERROR in SQL Server 2005 is largely the same as for SQL 2000. However, instead of 400 characters, you have 2047. If you use 2048 or more, then 2044 are displayed along with an ellipsis.

RAISERROR will cause the code to jump from the TRY to the CATCH block.

Because of the new error handling capabilities, RAISERROR can be called in a more efficient manner in SQL Server 2005. This from the Books Online:

SQL Server 2000 – RETURNING ERROR CODES FROM STORED PROCEDURES

Stored procedures, by default, return the success of execution as either zero or a number representing the failure of execution, but not necessarily the error number encountered.

This will cause an error and the SELECT statement will return a non-zero value. On my machine, -6. In order take control of this, modify the procedure as follows:

This time the SELECT @err statement will return the 547 error number in the results. With that, you can begin to create a more appropriate error handling routine that will evolve into a coding best practice within your organization.

SQL Server 2005 – RETURNING ERROR CODES FROM STORED PROCEDURES

In order to appropriately handle errors you to know what they are. You may also want to return the errors to the calling application. A number of new functions have been created so that you can appropriately deal with different errors, and log, report, anything you need, the errors that were generated.

When you run the code above, you should receive this on the client, in the message, with a non-zero number in the result set:

In other words, everything you need to actually deal with errors as they occur.

You’ll also notice that the procedure returned an error value (non-zero) even though we didn’t specify a return code. You can still specify a return value as before if you don’t want to leave it up to the engine.

SQL Server 2000 – TRANSACTIONS AND ERROR TRAPPING

The one area of control we do have in SQL Server 2000 is around the transaction. In SQL Server 2000 you can decide to rollback or not, those are your only options. You need to make decision regarding whether or not to use XACT_ABORT. Setting it to ON will cause an entire transaction to terminate and rollback in the event of any runtime error. if you set it to OFF, then in some cases you can rollback the individual statement within the transaction as opposed to the entire transaction.

Modify the procedure to handle transactions:

Since the above code will generate an error on the second statement, the transaction is rolled back as a unit. Switch to the results in order to see that the zip code is, in fact, still 90210. If we wanted to control each update as a seperate statement, in order to get one of them to complete, we could encapsulate each statement in a transaction:

In this case then, the return value will be ‘90210’ since the first update statement will complete successfully. Be sure that whatever mechanism you use to call procedures does not itself begin a transaction as part of the call or the error generated will result in a rollback, regardless of the commit within the procedure. In the next example, we’ll create a transaction that wraps the other two transactions, much as a calling program would. If we then check for errors and commit or rollback based on the general error state, it’s as if the inner transaction that was successful never happened, as the outer transaction rollback undoes all the work within it.

SQL Server 2005 – TRANSACTIONS AND ERROR TRAPPING

The new error handling changes how transactions are dealt with. You can now check the transaction state using XACT_STATE() function. Transactions can be:

• Closed (equal to zero (0))
• Open but unable to commit (-1)
• Open and able to be committed (1)

From there, you can make a decision as to whether or not a transaction is committed or rolled back. XACT_ABORT works the same way.

SQL Server 2005 – EXTENDED 2005 ERROR TRAPPING

With the new TRY...CATCH construct, it’s finally possible to do things about errors, other than just return them. Take for example the dreaded deadlock. Prior to SQL Server 2005, the best you could hope for was to walk through the error messages stored in the log recorded by setting TRACEFLAG values. Now, instead, you can set up a retry mechanism to attempt the query more than once.