April 1, 2004 at 9:07 pm
Hi,
My intension is to loop thru records and make set of updates and if that is sucessful then commit the record and move on to next record and in case of failure rollback the changes made to that particulal record and continue with next set of records.
The code template is as follows .
Looping is happening for the positive cases and it is not happening in case failed ones.
DECLARE CURSOR
FOR
<SELECT STATEMENET>
OPEN <Cursorname>
FETCH NEXT FROM <Cursorname> INTO <Variables>
WHILE (@@FETCH_STATUS=0 )
BEGIN
BEGIN TRAN <NAME>
<Processing>
COMMIT TRAN<NAME>
FETCH NEXT FROM <Cursorname> INTO <Variables>
END
GOTO CloseCursor
ERROR:
ROLLBACK TRAN <NAME>
FETCH NEXT FROM <Cursorname> INTO <Variables>
CloseCursor:
<Close and deallocate >
April 2, 2004 at 4:08 am
Just try this out and lemme know it worked r not...
DECLARE CURSOR FOR
SELECT STATEMENET
OPEN Cursorname
FETCH NEXT FROM Cursorname INTO Variables
WHILE (@@FETCH_STATUS=0 )
BEGIN
BEGIN TRAN
COMMIT TRAN
ERROR:
IF @@ERROR 0
BEGIN
ROLLBACK TRAN
END
FETCH NEXT FROM INTO
END
April 2, 2004 at 5:43 am
do not forget to add
SET XACT_ABORT OFF
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply