SQL Server error handling, or exception handling, has always been a tricky part of T-SQL development. It used to be that you had to manually check for errors after running each statement and handle them on a case-by-case basis. SQL Server transaction management was also a chore because you had to figure out the state of your transactions and roll them back or commit them as appropriate.
With SQL Server 2005, Microsoft gives us newer, more robust error handling capabilities by adding Try...Catch blocks to T-SQL. In this tip, we will look at how Try...Catch blocks work and how you can put them to use in your code. We will also look at some comparisons between the old ways and the new ways so you'll have a better understanding of how using Try...Catch can bring your error handling into the 21st century.
Try...Catch defined
In order to understand Try…Catch blocks, you have to understand exception handling. Exception handling is simple really: When something unplanned occurs during the execution of your code, an exception, you need to have a routine that can handle that exception. How you handle the exception is up to you as the developer.
In order to add exception handling to your T-SQL code, you use Try…Catch blocks. If you have used Visual Studio recently, you are probably familiar with the concept of Try…Catch blocks. Their implementation in T-SQL is similar but actually has fewer options than the full-blown implementation you would encounter in Visual Studio. To use Try…Catch, put the T-SQL code you want to run inside of a Try block. If an error with a severity of 10 or higher occurs during the execution of the Try code, execution is passed to exception handling code in a Catch block. The basic construct is shown below:
When using Try…Catch in T-SQL, here are a few crucial points to keep in mind:
Try blocks must be followed directly by a Catch block, anything else will...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
cause an error.
Try…Catch cannot span batches.
If the Try code runs without fail, execution passes to the first line following the end of the Catch block.
When Catch code completes, execution passes to the first line following the end of the Catch block.
Handling errors
When an error occurs, you as the developer need to decide how to deal with it. Since you cannot pass control back to code that caused the error, like you can in .NET languages, you will likely log the problem and roll back any transactions that may be in-flight. To help with logging, there are several system functions that can provide more information about an error. The available system functions are detailed below:
ERROR_NUMBER() - Returns the error number.
ERROR_SEVERITY() - Returns the error's severity.
ERROR_STATE() - Returns the error's state.
ERROR_PROCEDURE() - Returns the name of the stored procedure or trigger in which the error occurred.
ERROR_LINE() - Returns the line number on which the error occurred.
ERROR_MESSAGE() returns the actual text of the error message.
Using these functions, you can record the details of the error and return that information to the caller of the procedure or log it for troubleshooting purposes. The functions will only work when called inside a Catch block; trying to call them otherwise would return NULL. They will, however, work within the scope of the code running in the Catch block. This means that you can call a stored procedure to handle the error and that stored procedure can access the error functions. Here is an example of just such a stored procedure:
This procedure can then be called in your Catch block to return and log the error details.
BEGIN TRY
--INSERT CODE HERE: When an error occurs, control will be passed
to the Catch block
END TRY
BEGIN CATCH
--This proc will log the error and send the details back to the
calling application
EXEC spLogError
END CATCH
Using the XACT_STATE() function
There is one more error function that we have not discussed, the XACT_STATE() function. This function can be called in your Catch blocks to return the exact transaction state of your procedure. The return code from XACT_STATE() will have one of the following values:
1 : There is an active transaction that could be either committed or rolled back.
0 : There is no active transaction.
-1 : There is an active transaction, but errors have occurred and the transaction cannot be committed.
Based on the return of this function, you can handle your in-flight transaction. If you receive a 1, you can roll back or commit as normal. If you receive a 0, there are no open transactions, and attempting a commit would generate an error. The special case is -1. That means there was a transaction, but it is not committable. You are also unable to roll back to a save point when you receive a -1 code; the entire transaction must be rolled back.
Error and exception handling is crucial to any good coding standards. Now with SQL Server 2005 you can implement advanced error handling using Try…Catch blocks.
ABOUT THE AUTHOR:
Eric Johnson (MCSE, MCITP: Database Administrator, MCSD) is co-founder of Consortio Services and the primary Database Technologies consultant. He has delivered numerous SQL Server training classes and webcasts as well as presentations at national technology conferences. Eric has presented at TechMentor on SQL Server 2005 Replication, Reporting Services, and Integration Services. He is president of the Colorado Springs SQL Server Users Group.
You can see Eric at the Highly Available SQL Solutions seminar in Denver, CO on July 26 and 27. Learn rock solid solutions with in depth coverage of clustering, mirroring, log shipping, replication and disaster recovery.
