Subscribe to this blog
Briefcase
Print
This is based on the original post in Franglais.
The goal of this post is to explain how to handle errors thanks to a useful T-SQL functionality, available since SQL 2005 onwards, that developers are already used to – and similar to how exceptions are handled in the Visual Studio environment.
If you are used to using @@error raiserror in previous versions of SQL Server, then the probably the best thing for you to use now is TRY/CATCH. Not to be used everywhere, just when you have to run multiple inserts/updates that are critical and that you anticipate errors for that code. For just a single insert/update within a proc, then just use begin / end and do not go crazy with it everywhere.
Begin Try
Block of T-SQL code that can be an issue
End Try
--when there is a problem with the above (as soon as there is an error):
Begin Catch --
Bloc du code T-SQL -- useful sysfuntions are available, VS programmers like this...
-- Error_Number/Severity/State/Procedure/Line/Message() here use these functions
-- to throw back info to the application as necessary
End Catch
The typical method of control is the Begin Transaction/Commit, but you can also take advantage of Try/Catch block. An exception will not automatically cause the transaction to rollback - this is a common myth...things will keep running afterwards and a commit will happen with the part of code you really needed to run before. One can also use XACT_ABORT whenever you are not using TRY/CATCH and are dealing with transactions (thanks to Adam Machanic MVP for clarifying this in his book Expert SQL Server 2005 Development page 62-72). In other words, use only one of the two at the same time: either set XACT_ABORT ON or your Try/Catch block.
Nota Bene: Compilation errors or syntax are not handled in the Try/Catch block, so make sure to sort those out before using this functionality. It's used to handle transactions that are prone to exceptions. If you have logical errors, then TRY/CATCH is not your saviour.
References :
http://msdn.microsoft.com/en-us/library/ms175976.aspx
http://www.sql-server-performance.com/articles/per/deadlock_sql_2005_p1.aspx TRY/CATCH Helps to Resolve Deadlocks
http://www.databasejournal.com/features/mssql/article.php/3587891
http://blog.sqlauthority.com/2007/04/11/sql-server-2005-explanation-of-trycatch-and-error-handling/
Posted by erez_bat on 11 June 2009
Hi
Very useful article.
I happaned to deal with the issues discussed here lately.
Intresting thing I found out when using begin & commit tran -
I have "planted" an error by first dropping a certain table, which I have tried to drop second time inside the transaction (Though it didn't exist anymore)
Although I got an error message, the tran continued normally, after the drop command line.
Same thing I have done with truncate command instead of the drop command.
This time the tran stopped immediately.
Than I found out about try and catch.I have put roolback
inside the catch block.As it said in the article this is the way to deal with similar situations, like the obe I've mentioned.
Erez
Posted by Hugo Shebbeare on 12 June 2009
Thanks Erez, I do my best to please:)
Posted by sue liao on 18 July 2009
What happen if we use Try & Catch everywhere, there are only performance hurt for SQL server if we do?
Thanks
Posted by Slick84 on 21 July 2009
"In other words, use only one of the two at the same time: either set XACT_ABORT ON or your Try/Catch block."
Your last two lines answered my question. That's what I was exactly looking for but couldnt find an answer that if I needed to combine both or do either one of those. Thanks for the article.
Posted by Slick84 on 21 July 2009
This might be a basic question, but I'm wondering if I can put 2 of my update statements and 4 insert statements within the same BEGIN TRY 'code' END TRY block? Or do I need to have seperate TRY's for Updates and Inserts?
From my understanding of reading different articles including this one, it seems I can put all under one try. Please confirm if possible. Thanks.
Posted by Hugo Shebbeare on 22 July 2009
Sure Omair, you can do that. Only problem is that you won't have the chance to know which of the statements had a failure...in other words, what is the insert or the update that failed (or maybe you don't care, it all has to run or nothing I can assume).
Posted by Omair Aleem on 12 August 2009
Your assumption was correct Hugo. I'm taking a all-or-nothing approach to this. Adding a Try/Catch to every statement would make it hard to read the code. I'm trying to keep this as simple and as easy to interpret as possible even for junior developers, etc.