Why Generic Error Handling?
-
I see a lot of code with generic error handling for every insert, update, or delete statement. What is the point of this code:
begin tran
insert X into table
set @err = @@error
if @err <> 0
begin
rollback
raiserror or whatever else you want to do
else
commit tran
end
If there is a problem with the insert, sql server is going to stop the transaction and roll it back. It will also stop the stored procedure from continuing and display an error relating to what happened. It never reaches the check for @@error.
Am I wrong in thinking that attaching this generic error handling to all insert/update/deletes is a waste of time? Are there times when things get through the insert w/out problem, but you want to cancel the sproc and rollback?
Thanks,
Chris
-
That Error handling should "In my opinion" be used only when you are trying to perform several DML as a single transactional action. You should not need that for a single DML operation because all SQL Server DML are A.C.I.D.
hth
* Noel -
Even for multiple transactions, couldn't you just do
Begin Tran
insert
update
delete
comit
if there's an error in any of the 3 commands, won't it rollback the entire transaction?
Thanks
-
I would point you to this article
http://www.sqlservercentral.com/columnists/dpeterson/allabouttransactionspart2.asp
hth
Dave
-
Christopher,
When you use EXPLICIT Transaction commands you have to call COMMIT or ROLLBACK after you call BEGIN TRANSACTION.
Lets assume that in "your post" you get an error in the middle ( the update statement). If you don't call rollback immediately the delete will execute and may be successfull then in the end when you call Rollback (I hope you do) will undo the delete performed and the initial insert. That does not sounds pretty, right ?
hth
* Noel -
I'd like to add a somewhat generic opinion to the original question "Am I wrong in thinking that attaching this generic error handling to all insert/update/deletes is a waste of time? Are there times when things get through the insert w/out problem, but you want to cancel the sproc and rollback?"
.. and to that just say - Yes. ..there might very well be.
imnho more errorhandling is better than less - nothing ever got corrupted by having 'unnecessary' errorhandling, but failing just one spot just might do just that.
One must also place the concept of 'what is an error' in it's proper context for each case. The simple insert example by it's own may not need explicit errorhandling, but what about the overall scope? If it's just one part of something more, then there may very well be that one would want programmatic control.
In general, I don't see explicit errorhandling in any form as either 'a waste of time' or 'unnecessary'. What is more dangerous, is developers omitting being clear and concise in their writing, instead relying on supposed default behaviour of the product/platform/language/whatever. Those are the things that will break eventually.
just my .02 anyways
/Kenneth
-
Is this the same question as on SQL Server Performance.com?
http://www.sommarskog.se/error-handling-II.html
http://www.sommarskog.se/error-handling-I.html
offer a good insight on error handling.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
thanks for all of your responses. The article on sqlservercentral....all about transactions was GREAT! I came up with a standard for our SQL developers based on what I learned.
Chris
-
Can you post it here so that others can benefit from your efforts?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply