Cursor quits fetching records on rollback

[zrefugee]

I'm trying to populate parameters for a stored procedure by using a cursor to pull data that has certain flags set. I have a commit/rollback inside the while loop of the cursor. At the first rollback for an insert failure, the loop quits fetching records. Is there anyway for fetching to continue after a record failure. I have the code below. Thanks for any help.

DECLARE CSR_VALID_LABELS CURSOR
FOR
SELECT PRI_KEY, CUST_ITEM, QUANTITY, SERIAL_NUMBER, EC_LEVEL, JOB FROM SF_LABELS WHERE VALID = '1' AND EDI != 2 ORDER BY PRI_KEY DESC

Open CSR_VALID_LABELS

DECLARE @ITEM_CNT INT, @CNT INT, @ERR INT, @RESULTS VARCHAR(50), @PRI_KEY numeric (9), @CUST_ITEM varchar(30), @QUANTITY float, @SERIAL_NUMBER varchar(30), @EC_LEVEL varchar(20), @JOB varchar(20)

FETCH NEXT FROM CSR_VALID_LABELS INTO @PRI_KEY, @CUST_ITEM, @QUANTITY, @SERIAL_NUMBER, @EC_LEVEL, @JOB

WHILE (@@FETCH_STATUS <> -1)
BEGIN
-IF (@@FETCH_STATUS <> -2)

SET XACT_ABORT ON

BEGIN TRANSACTION REP_EDI

UPDATE SF_LABELS SET EDI = '1' WHERE PRI_KEY = @PRI_KEY --BUSY
EXEC ESI_AddLabelRecord @RESULTS OUT, @CUST_ITEM, @QUANTITY, @SERIAL_NUMBER, '', '', @EC_LEVEL, @JOB, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''
UPDATE SF_LABELS SET EDI = '2' WHERE PRI_KEY = @PRI_KEY --COMPLETED
IF(@@ERROR > 0)
BEGIN
ROLLBACK TRANSACTION REP_EDI

END
ELSE COMMIT TRANSACTION REP_EDI

FETCH NEXT FROM CSR_VALID_LABELS INTO @PRI_KEY, @CUST_ITEM, @QUANTITY, @SERIAL_NUMBER, @EC_LEVEL, @JOB
END

CLOSE CSR_VALID_LABELS
DEALLOCATE CSR_VALID_LABELS

 

[fredericofonseca]

try
SET CURSOR_CLOSE_ON_COMMIT OFF
before opening the cursor.

Not sure if it will work as you expect.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[fredericofonseca]

and to add some info.


The effect of a ROLLBACK on cursors is defined by these three rules:

With CURSOR_CLOSE_ON_COMMIT set ON, ROLLBACK closes, but does not deallocate all open cursors.


With CURSOR_CLOSE_ON_COMMIT set OFF, ROLLBACK does not affect any open synchronous STATIC or INSENSITIVE cursors or asynchronous STATIC cursors that have been fully populated. Open cursors of any other type are closed but not deallocated.


An error that terminates a batch and generates an internal rollback deallocates all cursors that were declared in the batch containing the error statement. All cursors are deallocated regardless of their type or the setting of CURSOR_CLOSE_ON_COMMIT. This includes cursors declared in stored procedures called by the error batch. Cursors declared in a batch before the error batch are subject to rules 1 and 2. A deadlock error is an example of this type of error. A ROLLBACK statement issued in a trigger also automatically generates this type of error.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com