October 21, 2010 at 3:57 am
I generally use the following template for error handling in SQL Server 2008
CREATE PROCEDURE Sample
As
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRAN
-- Multiple DML
INSERT INTO TabA ....
INSERT INTO TabB
COMMIT TRAN
RETURN(50)
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN
RAISEERROR(......)
RETURN(10)
END CATCH
END
I also found in MSDN it is suggested to use SET XACT_ABORT ON . I can understand its purpose with reference to SQL Server 2000. I also know we can COMMIT a transaction even in CATCH block based on XACT_STATE().
If as per business and Design we are not willing to COMMIT any Transaction in CATCH block Then
-- TRAY .. CATCH with @@TRANCOUNT check in Catch block is sufficient ?
-- Is it beneficial to use XACT_ABORT and XACT_STATE in comparison to template I provided ?
Thanks in Advance.
Regards
BB
October 28, 2013 at 7:32 am
(In case someone else stumbles across this)
I believe one fundamental difference (if I'm reading BOL right - if not, someone please correct this) is that XACT_STATE can detect uncommittable transactions (XACT_STATE = -1) whereas @@TRANCOUNT detects if there are active transactions, but not whether they are committable.
So from my limited understanding, you'd probably use @@TRANCOUNT to rollback active transactions in a CATCH block no matter what, and XACT_STATE to commit if possible despite the error.
Update BOL also states XACT_STATE does not detect nested transactions while @@TRANCOUNT does.
____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply