Try | Scratchbox | Zach Stagers

The TRY…CATCH code was introduced in SQL 2005 as an easy way to manage errors. There are several system functions that go along with the TRY…CATCH which provide much of the necessary information to resolve any errors that may arise.

I’ll first discuss the how the TRY…CATCH works, and its syntax.

There are two parts to a TRY…CATCH, and if it isn’t already obvious enough – they are the TRY, and the CATCH. The TRY just encapsulates your SQL, the code you’re trying to execute. The CATCH is where things get much more interesting.

First, the SQL contained within the TRY block is executed (The TRY tries to execute the SQL – hence the name). If the TRY fails for any reason, then control is immediately passed to the CATCH block where you can output any relevant information, or do as much damage recovery as possible. Note that only errors with a severity over 10 will push control over to the CATCH, any error with a severity below 10 will just output its usual error message.

The syntax is easy enough, as shown in the example below:

BEGIN TRY
	PRINT 7/0
END TRY
BEGIN CATCH
	INSERT INTO ErrorLog
	(ErrorLine, ErrorMessage, ErrorSeverity, ErrorState, ErrorNumber)
	VALUES(ERROR_LINE(), ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_NUMBER())
        PRINT 'An error occured, and has been logged'
END CATCH

Executing the above PRINT statement would normally produce the “Divide by zero error encountered.“, but using the Error functions listed in the CATCH, you can control what you do with the errors values. In the example, the details of the error are inserted into an error log table, and a message is displayed letting the user know so.

The Error Functions

I’ve used them in an example, but you may be wondering exactly what each of them return. Some of them are more useful than others, and this changes depending on the situation. But I’ll give a brief description of each;

ERROR_LINE() – This is one of my favorites, and can be very handy in stored procedures with many lines of code. This function will return the exact line that the error occured on. See “Enabling Line Numbers in SSMS 2008“
ERROR_MESSAGE() – This provides the text that is normally produced by the error that occured, for the example provided in this post, “Divide by zero error encountered.” would be returned
ERROR_SEVERITY() – Returns the severity of the error. Remember that only errors with a severity over 10 will actually pass control to the CATCH. The CATCH will also not be invoked if the error produced causes you to lose your connection with the database.
ERROR_STATE() – If the same error number & message is capable of being raised by different causes, this will return an integer value which should help you find more information on the error you’ve recieved and what caused it.
ERROR_NUMBER() – Returns the errors number, again for our divide by zero error – “8134″ would be returned here.

There are a two other functions that I’d like to mention, but have not yet given examples of.

The first is XACT_STATE(). This function returns a tiny integer (-1, 0, or 1), and knowing what they mean allows you to have further control over your queries.

If the XACT_STATE() returns a 0, then there isn’t currently an open transaction.
1 would mean that there is an open transaction, and it’s in a commitable state.
-1 means that there is an open transaction, but it isn’t in a commitable state.

Knowing this allows you to do something like the below in your CATCH if you’re using transactions:

BEGIN CATCH
      IF (XACT_STATE()) = 1
        BEGIN
         COMMIT TRANSACTION
        END
 
      IF (XACT_STATE()) = -1
        BEGIN
         ROLLBACK TRANSACTION
        END
END CATCH