April 8, 2008 at 10:14 am
Hi,
This is with respect to the Error handling with respect to SQL 2005. I have been reading a lot about the same and was getting a bit confused
Do I need to have a BEGIN TRAN even if I am using TRY CATCH block
This is how I implement my Procs
CREATE PROCEDURE ...
AS
SET NOCOUNT ON
BEGIN TRY
---- SQL Statements ---
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
BEGIN
ROLLBACK TRANSACTION
END
END CATCH
Is this the right way.
1) Do i need to explicitly add the begin TRAN and COMMIT TRAN and check for all the other values of XACT_STATE
Thanks
VInoj
April 8, 2008 at 10:57 am
Yes
BEGIN TRAN
BEGIN TRY
---- SQL Statements ---
COMMIT TRAN
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
BEGIN
ROLLBACK TRAN
END
END CATCH
April 8, 2008 at 1:47 pm
Thanks for the reply. What if I was using SET XACT_ABORT ON, Do I still need to use the Begin TRAN and COMMIT
CREATE PROCEDURE ...
AS
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRY
---- SQL Statements ---
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
BEGIN
ROLLBACK TRANSACTION
END
END CATCH
Thanks
Vinoj
April 8, 2008 at 1:54 pm
XACT_ABORT ON specifies that if an error is encountered any open transaction will be rolled back.
You still need to have an open transaction.
Depending on connection settings you may have a transaction of some sort open, but probably not.
If the global variable @@trancount is zero, you need to begin a transaction to have something to roll back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply