Do you use both SET XACT_ABORT ON and Try/Catch block in your SQL Sever 2005 stored procedure?
Well, I found that in most cases using both in the same procedure is unncessary. Why?
When you use SET XACT_ABORT ON, on any error sql server terminates the currently executing batch and no statement after the statement on which the error occured will be execued. This means that if the error occurs in the TRY block, then the CATCH block will not get executed if the XACT_ABORT setting is ON. Now, if you just need to rollback the transaction in case of any error then setting the XACT_ABORT to ON is enough.
Actually the main perpose of using Try/Catch block is to do error handling. But when you set the XACT_ABORT setting to ON, then your error handling code is not going to execute.