July 7, 2009 at 10:26 am
Hey guys, I have a question regarding sql server transactions, specificly for the objects that execute scripts suchs as stored procedures or triggers, when I create a sp that modifies data is it recomendable to set a begin transaction and commit transaction (asuming it is called from a client application that opens a connection first in .Net)? if so is it necessary to add a @@Error validation to make a rollback or commit? I wonder this beacause usually when there's an error (like PK violation for example) SQL automaticaly does a rollback (or does this deppends on the connection string).
Can someone tell me?
Thanks!
July 7, 2009 at 10:37 am
Using @@Error will be good practice because the database objects wont rollback if there is an error in the script, That time either we neeed to check from sysobjects or information_schema to drop the existing one.
So better if we use inside atrasaction with @@error clause so that the complete operation will be rolled back
July 7, 2009 at 10:40 am
Not all errors roll the transaction back. You should use begin transaction and rollback/commit if it needs to be an atomic operation (all succeeds or none succeeds)
On 2005, Try.. catch is preferred over @@error. It's easier to use.
If you do go with @@Error, it has to be checked after every statement as it contains the error code for the previous statement only.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 7, 2009 at 10:41 am
Got it, thanks guys 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply