What is the difference between SET xact_abort ON and try/catch block with Transaction handling in sqlserver 2005?

Question

Hi all, I need to improve some existing stored procedures in my project for better transaction handling. I understand I can use the SET XACT_Abort ON statement in my procedure so that transaction will be automatically rolled back in case of errors. I can also use Try/Catch block for error handling and roll back the transaction in the Catch block in case of errors? My question what is the main difference between these two and why I should use one over the another? Are there any guidelines that I should use when deciding between these two?

Answer 1

Try/Catch blocks are new with SQL server 2005 and allow you to handle errors as opposed to just having them rolled back - Try/Catch blocks restrict you to a single batch, but of course that's moot within a stored procedure. If your procedures must remain compatible with previous versions of SQL server, you might consider XACT_ABORT if it helps, but I would submit that Try/Catch is the way to go going forward.