MySQL cursor fetching same result twice

Question

I've got a Stored Procedure that checks rows from one table to insert its details into another. I'm using a cursor but I have a big problem: the cursor loops 2 times over the same row. So I get 2 repeated inserts . Here is the sp code:

    IF (SELECT 1 FROM NOVEDADES  WHERE LEGAJO_ID = pLEGAJO_ID AND FECHA >= pFECHA AND CONCEPTO_ID != 11 AND CONCEPTO_ID != 13 AND CONCEPTO_ID != 12 LIMIT 1) = 1
    THEN
        BEGIN
        DECLARE vCONCEPTO_ID INT;
        DECLARE vMONTO DECIMAL(12,2);
        DECLARE vID INT;
        DECLARE vDONE INT DEFAULT 0;

        DECLARE CURSOR_NOVEDADES CURSOR FOR     

        SELECT ID
        FROM NOVEDADES 
        WHERE LEGAJO_ID = pLEGAJO_ID
        AND FECHA >= pFECHA
        AND CONCEPTO_ID != 11 
        AND CONCEPTO_ID != 13 
        AND CONCEPTO_ID != 12;

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET vDONE=1;

        OPEN CURSOR_NOVEDADES;

        SET vDONE = 0;

        REPEAT

            FETCH CURSOR_NOVEDADES INTO vID;

            SELECT CONCEPTO_ID, MONTO INTO vCONCEPTO_ID, vMONTO
            FROM NOVEDADES WHERE ID = vID;

            INSERT INTO LIQUIDACIONES_DETALLE (LIQUIDACION_ID, CONCEPTO_ID, MONTO)
            VALUES(pLIQUIDACION_ID, vCONCEPTO_ID, vMONTO);

        UNTIL vDONE END REPEAT;
        CLOSE CURSOR_NOVEDADES;
        END;
    END IF;

variables beggining with "p" are IN parameters, with "v" are common variables. I must say that the query of the cursor returns only 1 value. I've tried with LOOP also, but same result. I've tried "debugging" the procedure inserting some SELECTS and I see the repeated.

Thanks a lot.

how do you know it is repeating ? do you have duplicate rows in LIQUIDACIONES_DETALLE ? — Stephan
– Stephan, Commented Jun 3, 2013 at 15:44

Gordon Linoff · Accepted Answer · 2013-06-03 15:45:42Z

7

On the last iteration through the loop, the fetch is failing. When it does so, you are re-inserting the previous values. Here is one way to fix this:

    REPEAT

        FETCH CURSOR_NOVEDADES INTO vID;

        if ! vdone then

            SELECT CONCEPTO_ID, MONTO INTO vCONCEPTO_ID, vMONTO
            FROM NOVEDADES WHERE ID = vID;

            INSERT INTO LIQUIDACIONES_DETALLE (LIQUIDACION_ID, CONCEPTO_ID, MONTO)
            VALUES(pLIQUIDACION_ID, vCONCEPTO_ID, vMONTO);
       end

    UNTIL vDONE END REPEAT;

answered Jun 3, 2013 at 15:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

leandronn Over a year ago

Thanks, I'll try and let you know.

Collectives™ on Stack Overflow

MySQL cursor fetching same result twice

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related