August 4, 2008 at 9:18 am
What is the difference between these two approaches? The use of @@TRANCOUNT seems legitmate to me but looking at the approach used by in the URL makes me wonder if I have missed something. (The URL approach certainly looks more thorough but I don't know if it is "btter" than the @@TRANCOUNT approach).
TIA,
Barkingdog
1.
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
...
...
2. From http://msdn.microsoft.com/en-us/library/ms179296.aspx
BEGIN CATCH
SELECT
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage;
-- Test XACT_STATE for 1 or -1.
-- XACT_STATE = 0 means there is no transaction and
-- a commit or rollback operation would generate an error.
-- Test whether the transaction is uncommittable.
IF (XACT_STATE()) = -1
BEGIN
N'The transaction is in an uncommittable state. ' +
'Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
-- Test whether the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
N'The transaction is committable. ' +
'Committing transaction.'
COMMIT TRANSACTION;
END;
END CATCH;
August 4, 2008 at 11:35 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply