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 |
|
kalyformin
Starting Member
15 Posts |
 Posted - 2007-12-15 : 14:30:03
|
| I have a cursor loop through a set of records that looks something like this.OPEN database_cursorFETCH NEXT FROM database_cursor INTO @iID WHILE @@FETCH_STATUS = 0BEGIN update table 1 update table ......FETCH NEXT FROM database_cursor INTO @iIDENDCLOSE database_cursorDEALLOCATE database_cursorIs there a way i could put all the UPDATE statements within a transaction. either everything goes or nothing.THnaksThanks, |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-12-15 : 15:11:33
|
| why have a cursor at all?set xact_abort onbegin tran--your stuffcommitset xact_abort on means that the transacion will rollback if an error happens._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out |
 |
|
|
kalyformin
Starting Member
15 Posts |
Posted - 2007-12-15 : 16:58:12
|
| I have the cursor to perform above actions in a loop for a specific condition. I'd like to commit/rollback changes if the script execution is stopped or gets killed for some reason. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-12-15 : 18:35:20
|
YOu still don't need a cursor, you can update using a where clause to specify the conditions. SQL is works best with SET based operations, not row level operations which are more intensive for resource use.If the process is killed, it will rollback by default. Or as suggested you place the Begin Trans/ Commit lines in...if it fails or is otherwise cancelled, the operation will rollback.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
|
|
|
Commit and Rollback