GilaMonster (10/15/2013)
lnardozi 61862 (10/15/2013)
Without them, the transaction is left open if an error occurs during processing.Unless XACT_ABORT is on.
Can I just check. In the pseudo code below, assume Col1 is an int
DECLARE @ReturnValue int
BEGIN TRANSACTION
INSERT INTO MyTable(Col1)
VALUES (25)
IF @@ERROR <> 0
BEGIN
SET @ReturnValue = -999
ROLLBACK TRANSACTION
RETURN @ReturnValue
END
INSERT INTO MyTable(Col1)
VALUES (17)
IF @@ERROR = 0
BEGIN
SET @ReturnValue = -998
ROLLBACK TRANSACTION
RETURN @ReturnValue
END
COMMIT TRANSACTION
Are you saying that unless XACT_ABORT is ON - in the example above, the first insert will not be rolled back?
I have just put the code above into a stored procedure and executed it. -998 is returned. No records are written into MyTable. XACT_ABORT is off.