Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vivo
Starting Member
24 Posts |
 Posted - 2010-03-28 : 08:09:37
|
| Can anyone let me know how to use roll back and commit tran in stored procedure if I have more than 1 transaction.Now I need to have-drop index-trancate table-create index-insert records where should i use commit and rollback?Thanks in advance |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-03-28 : 09:09:19
|
| It's hard to say without knowing your specific needs. You use a transaction to protect yourself against a partial failure of your procedure. If you procedure make several changes, and one of those changes fails, then you will want to undo some or all of the other changes that have already succeeded. eg, the sp may log details of who called it before proceeding with you changes. This may not be something you'd want to roll back, because you may still want to know details about calls that failed. However, if the procedure changes 2 separate records, for example debiting one account and crediting another, then a failure on the second change should undo the first.With your drop index/trancate table/create index/insert records procedure, how often do you plan on calling it, and how many records will be inserted. Why do you wish to drop and recreate the index? Is the insert an all or nothing process? Will they be inserted individually, or in a single bulk statement? There are 10 types of people in the world, those that understand binary, and those that don't. |
 |
|
|
|
|
|
Commit and Rollback