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 |
|
sunwerby
Starting Member
2 Posts |
 Posted - 2012-01-20 : 03:27:00
|
| Hi!Is it possible to roll back only the outermost transaction in a prosedure that has nested trenstactions. I have a stored prosedure that calls another prosedure. When I roll back the outermost transaction all the inner transactions are also rolled back. How can I roll back only the outermost, but not the inner transactions?Thanks in advance! |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-20 : 04:15:49
|
| Nested transactions are a lie, they don't really exist. It's just some syntax that makes it look like they do.Look up savepoints if you want to roll back to a point within a transaction.--Gail ShawSQL Server MVP |
 |
|
|
sunwerby
Starting Member
2 Posts |
Posted - 2012-01-20 : 04:49:33
|
| Thanks!But my problem is: In my outermost prosedure I insert a record in a table, and I call a prosedure to make a test. If the test is true I want to keep my row inserted, if not i want to roll back. But when the test is false the inner prosedure write some data to another table and when I roll back this insertion is also rolled back, and I want to roll back only the outermost insertion. How can I solve this? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-20 : 05:22:04
|
| The only way I know to solve that is to put the data you want to "preserve" into an @TableVariable, then do the ROLLBACK (which will not rollback the @TableVariable, then re-insert/update the rows from the @TableVariableROLLBACK will reverse everything since the previous BEGIN TRANSACTION / SAVEPOINT, you can't choose to "preserve" some of it.You could manually delete/reverse the bits you don't want when you encounter the error-state in the outer most transaction, but I suspect that would be a bit "hairy" !! |
|
|
|
|
|
|
Rollback transaction