Today I want to start a short series of editorials on the topic of Exception, or error handling. I thought I would start out with SQL Server errors, and then move on later into the topic at large. SQL Server 2005 introduced a Try/Catch error handling syntax allowing us to easily capture errors are perform appropriate actions such as COMMIT or ROLLBACK.
BEGIN TRANSACTION
BEGIN TRY
--Some SQL
COMMIT
END TRY
BEGIN CATCH
-- Capture error data
ROLLBACK
-- do something with error data
END CATCH
Inside the CATCH section there are functions returning you information about the error that was thrown.
ERROR_MESSAGE() returns the text of the error
ERROR_STATE() returns the state of the error
ERROR_SEVERITY() returns the severity of the error
There are other functions returning additional attributes of the error. These are the ones I use most often. It would be worth your time to look at BOL for fuller descriptions of these attributes, because you can use them to throw your own error. There are other things you can do as well, but these are the simplest ones needed.
RAISERROR(‘[ERROR MESSAGE]’, [SEVERITY], [STATE])
A good point to remember from our discussion of transactions a few days back, it is best not to mix transaction handling in SQL with transaction handling outside of SQL Server, as in ADO or an ORM.
This leads me into a key thought I want to express in closing. In my experience, the sooner I develop and implement an error handling strategy, the more solid my application becomes. We’ll get into that in the future, because it is important to be able to capture technical details of errors so they can be fixed or ignored, without exposing the internal workings of your application to your end users. When errors are swallowed it makes it difficult to determine the cause of application bugs in many situations.
Add your thoughts about exception handling to our comments, or drop a note to btaylor@sswug.org.
Cheers,
Ben