HI,
Generally it is said, keep transactions short.
i noticed transaction starts from begin tran.
q1)so i wanted to know , keeping set options at the top is correct with respect to keeping transactions short
or can i bring few of them down?
q2)keeping set options at the top , above the variable declaration ?
q3) is there any of following.
--------------------------
SET NOCOUNT ON
SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
---------------------------------------
CREATE PROCEDURE abc
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
declare @a bigint
BEGIN TRY
BEGIN TRAN
.......
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
...........
END CATCH
SET XACT_ABORT OFF;
END
Q3) In above case which one is good.
IF @@TRANCOUNT >0 ROLLBACK TRANSACTION
or
IF ((XACT_STATE()) = -1 or (XACT_STATE()) =1)
ROLLBACK TRANSACTION
note: I want to rollback if control comes into catch block.
yours sincerely