Issue with cursor -

[Former Member]

Hi, My cursor stops after hitting a rollback. How can i continue processing after a rollback occurs?

Thanks, Greg

Here is my code:

DECLARE @supplied_date DATE SELECT @supplied_date = '2014-12-10' //Change the supplied date

DECLARE service_fut_curs CURSOR FOR SELECT service_code FROM service_future WHERE effective_date > @supplied_date

DECLARE @future_code        varchar(20)
DECLARE @existing_code      varchar(20)
DECLARE @rowcount           INT

OPEN service_fut_curs WITH HOLD         
FETCH service_fut_curs INTO @future_code

WHILE (@@sqlstatus = 0) 
BEGIN     
    SELECT service_code 
      INTO @existing_code
      FROM Service
     WHERE service_code = @future_code and active = 'Y'

    /* Move existing service to the service_hist table */
     INSERT INTO service_hist 
        (service_code, description, service_class, service_type, wt_low, wt_high, piece_cost, wt_cost, 
         vendor_id, ww_zone, last_ws, last_user, last_date, last_time, "comment", active, effective_date, country_code,
         level_id, bill_zone1, bill_zone2, bill_zone3, bill_zone4, bill_zone5, mail_class_id, pri_min_wt, pri_max_wt, actual_country_code, active_date,
         inactive_date, product_code, currency_code, volume_piece_min, volume_piece_max, matrix_id, piece, piece_wt, wt,
         hts_code, value_range_from, value_range_to) 
     SELECT service_code,description,service_class,service_type,wt_low,wt_high,piece_cost,wt_cost,
            vendor_id,ww_zone,last_ws,user,today(),now(),"comment",'N',effective_date,country_code,
            level_id,bill_zone1,bill_zone2,bill_zone3,bill_zone4,bill_zone5,mail_class_id,pri_min_wt,pri_max_wt,actual_country_code,active_date,
            inactive_date,product_code,currency_code,volume_piece_min,volume_piece_max,matrix_id,pieces,piece_wt,wt,
            hts_code,value_range_from,value_range_to 
       FROM service 
      WHERE service_code = @existing_code

    IF @@ROWCOUNT = 0
    BEGIN
        Select  'Warning: No rows were inserted into service_hist '
        ROLLBACK
    END

    IF @@ROWCOUNT = 1
    BEGIN
        Select  'Success: 1 row was inserted into service_hist '
        COMMIT

        -- Delete existing service from the service table
        DELETE FROM service WHERE service_code = @existing_code
        IF @@ROWCOUNT = 0
        BEGIN
            Select  'Warning: No rows were deleted FROM service'
            ROLLBACK
        END

        IF @@ROWCOUNT = 1
        BEGIN
            SELECT 'Success: 1 row was deleted FROM service'
            COMMIT
            --Insert New service into the service table
            INSERT INTO service (service_code,description,service_class,service_type,wt_low,wt_high,piece_cost,wt_cost,
                        vendor_id,ww_zone,last_ws,last_user,last_date,last_time,"comment",active,effective_date,country_code,
                        level_id,bill_zone1,bill_zone2,bill_zone3,bill_zone4,bill_zone5,mail_class_id,pri_min_wt,pri_max_wt,actual_country_code,active_date,
                        inactive_date,matrix_id,hts_code,value_range_from,value_range_to,pieces,piece_wt,
                        product_code,currency_code,volume_piece_min,volume_piece_max)
            SELECT service_code,description,service_class,service_type,wt_low,wt_high,piece_cost,wt_cost,
                   vendor_id,ww_zone,last_ws,user,today(),now(),"comment",'Y',effective_date,country_code,
                   level_id,bill_zone1,bill_zone2,bill_zone3,bill_zone4,bill_zone5,mail_class_id,pri_min_wt,pri_max_wt,actual_country_code,active_date,
                   inactive_date,matrix_id,hts_code,value_range_from,value_range_to,pieces,piece_wt,
                   product_code,currency_code,volume_piece_min,volume_piece_max
              FROM service_future 
             WHERE service_code = @existing_code

            IF @@ROWCOUNT = 0
            BEGIN
                Select  'Warning: No rows were inserted into the service table'
                ROLLBACK
            END

            IF @@ROWCOUNT = 1
            BEGIN
                Select  'Success: 1 row was inserted into the service table'
                COMMIT 
                      DELETE FROM service_future WHERE service_code = @existing_code 
                IF @@ROWCOUNT = 0
                BEGIN
                    Select  'Warning: No rows were deleted From service_future table'
                    ROLLBACK
                END

                IF @@ROWCOUNT = 1
                BEGIN
                    SELECT 'Success: 1 row was deleted From service_future table'
                    COMMIT
                END
            END -- check insert into service table
        END -- Check deleted FROM service'
    END -- check inserted into service_hist

    SELECT @rowcount =  @rowcount + 1
    FETCH NEXT service_fut_curs INTO @future_code        
END -- loop

CLOSE service_fut_curs         
DEALLOCATE service_fut_curs

[Former Member]

By default, cursors are closed at the end of transaction (commit or rollback). When you open a statement, you can specify that it be opened "WITH HOLD" to indicate it should remain open after the transaction.

Be aware that the semantics of a cursor being held open past a transaction, particularly after a rollback, are unusual and require special attention. The option ansi_close_cursors_on_rollback can be set to force even WITH HOLD cursors to close on ROLLBACK (this is the behaviour defined by the ANSI SQL Standard for WITH HOLD cursors). Some of these semantic issues can be sidestepped if you use an INSENSITIVE cursor type that would take a copy of the data at open time and not be sensitive to changes in the values of the referenced tables.

This is not in your question, but I occasionally am surprised when converting a FOR loop into an explicit cursor and forget to include WITH HOLD. The FOR loop implicitly opens the cursor with WITH HOLD and it will not close automatically after a commit. The cursor may be closed after a ROLLBACK depending on the ansi_close_cursors_on_rollback option.

[Former Member]

Thanks Ivan,

I was wondering if the there is another approach to handle this process. For each row that meets the cursor criteria, I need to:

1. Find the existing record in the service table
2. Move it to a history table
3. Delete existing row from the service table
4. Insert row into service table from the future table
5. Delete row from the future table

Thanks,

Greg

[Former Member]

This may be a good use case for DML-derived tables [1]. You could use one statement to delete rows from one table (service) and insert into the target (history). I haven't personally used the feature so I haven't crafted an example. I am not sure whether DML-derived tables can be used with the TSQL dialect (it appears your example uses TSQL dialect).

[1] http://dcx.sap.com/index.html#sa160/en/dbreference/from-statement.html*d5e55728

[VolkerBarth]

As we are using SQL, would it not be easier to use set statements, i.e. instead of using a cursor "one row at a time" approach, do all the "move" operations for all according row in one go?

One approach to do so would be to define a temporary table to identify all according rows (e.g. all "service_code" values) and then use joins to insert/delete all according rows from the according tables. (The temporary table would only be required if the matching rows may not be identified lateron, i.e. when the "moves" themselves would prevent the rows to be filtered again.)

Apparently, that would usually mean to use one transaction for all rows whereas a row-by-row approach might allow several rows to be moved whereas other row moves may be rolled back.

---

This is just a very general hint, without knowing the table schema (and particulary the FK relationship between tables "service", "service_future" and "service_hist" it's difficult to tell whether this will work for you...

---

Another approach would be to include all rows in the same table and use a status field to tell whether these are "historical" or "current" or "future" entries - "moving" would then be simplified to modify the status field.