CODEWITHSUNDEEP
mysqlstored-procedurescursor
Telemat
Telemat

Reputation: 398

MySQL Cursor Not Working

I am going nuts about this problem.

The stored procedure below opens a cursor, and then iterates over the result to do some inserts.

The select statement used for the cursor works when used outside a cursor (in the sql window). However it does not work in the cursor, the cursor returns NULL when opened!

Do note that this code is in a stored proc., that is itself called by another stored procedure which is opening another cursor. Not sure if this info. is useful.

DECLARE location VARCHAR(255);                      -- location
DECLARE isLocationDone BOOL DEFAULT FALSE;   -- status flag
DECLARE curLocation CURSOR FOR
    SELECT `LOCATION` AS loc FROM `mfdtemp`.`opexstk_tmp`
    WHERE `client` = in_client AND DATE(`rentdat`) < in_date
    ORDER BY loc;
DECLARE 
    CONTINUE HANDLER FOR SQLSTATE '02000' SET isLocationDone = TRUE;

OPEN curLocation;

label_location_loop: LOOP

    FETCH curLocation INTO location;

    IF isLocationDone THEN
        CLOSE curLocation;
        LEAVE label_location_loop;
    END IF;


    INSERT INTO t01_stock_report_htry (
        t01date, t01client, t01desc 
    ) 
    VALUES (in_date, in_client, CONCAT_WS('', 'Stack ', location)
    );

END LOOP;

Upvotes: 3

Views: 2238

Answers (2)

Atif Riaz
Atif Riaz

Reputation: 1

The answer is so simple that you will laugh at least once.

Please press Insert Button on the keyboard of your Pc

Upvotes: 0

eggyal
eggyal

Reputation: 125855

Why not just use INSERT ... SELECT?

INSERT INTO t01_stock_report_htry (t01date, t01client, t01desc)
  SELECT   in_date, in_client, CONCAT('Stack ', LOCATION)
  FROM     mfdtemp.opexstk_tmp
  WHERE    client = in_client AND DATE(rentdat) < in_date
  ORDER BY LOCATION

Upvotes: 1

Related Questions