The use of the two is slightly different. If you look at the XACT_STATE code, you'll notice that it has a commit statement if XACT_STATE = 1.
If you want to roll back, the @@Trancount code will do it for you. If you want to commit under certain circumstances and rollback in other circumstances, you can use the other version.
Personally, if I need to commit under certain circumstances and rollback under other, I controll that in the main body of the proc, and just roll back any/all uncommitted transactions in any Catch block. But I can see where it might, in theory, be useful to use the other way. Just can't think of a practical use for it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon