[Help] Call store procedure within cursor (SP use input parameters from cursor)

Hi all,

I've got a error below when i tried to call store procedure within cursor. Please show me what happen. Tks so much!
DROP PROCEDURE IF EXISTS prestashop.uspRetrieveData;
CREATE PROCEDURE prestashop.`uspRetrieveData`()
READS SQL DATA
BEGIN

DECLARE listpinIDs VARCHAR(255);
DECLARE orderid INT;
DECLARE done INT DEFAULT FALSE;
DECLARE c CURSOR FOR SELECT listpinID, preorderid FROM ps_payoo_preorder WHERE listpinid IS NOT NULL AND preorderid IS NOT NULL;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DROP TEMPORARY TABLE IF EXISTS tmpOrderMoneyTotal;
CREATE TEMPORARY TABLE `tmpOrderMoneyTotal`(`preorderid` int default NULL, `MoneyTotal` float default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;
OPEN c;
READLOOP: LOOP
FETCH c INTO listpinIDs, orderid;
IF done THEN
LEAVE READLOOP;
ELSE
SET @sqlStr = CONCAT('SELECT SUM(Price) INTO @MoneyTotal FROM ps_payoo_cards Card WHERE payoo_card_id IN (', listpinIDs, ')');

PREPARE stmt FROM @sqlStr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
INSERT INTO tmpOrderMoneyTotal(preorderid, MoneyTotal)
SELECT orderid, @MoneyTotal;
END IF;
END LOOP;
CLOSE c;

SELECT * FROM tmpOrderMoneyTotal;

END;

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

are you creating this in the mysql client? if so it is necessary to change the delimiter from ; to something else in order to use ; within your SP.

so at the top add DELIMITER & or some other character you aren't going to use in your SP at the bottom you'll need to type & for the SP to know you are finished and then type in DELIMITER ; to switch back.

Hi,
I added delimiter at the top and bottom of SP. it executed successful.But when I call "CALL uspRetrieveData()". I still get this error Are something wrong when I call @strStr winthin cursor declaration?


delimiter //
DROP PROCEDURE IF EXISTS prestashop.uspRetrieveData;
CREATE PROCEDURE prestashop.`uspRetrieveData`()
READS SQL DATA
BEGIN

DECLARE v_listpinIDs VARCHAR(255);
DECLARE v_orderid INT;
DECLARE v_done INT DEFAULT FALSE;
DECLARE v_c CURSOR FOR SELECT listpinID, preorderid FROM ps_payoo_preorder WHERE listpinid IS NOT NULL AND preorderid IS NOT NULL;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
DROP TEMPORARY TABLE IF EXISTS tmpOrderMoneyTotal;
CREATE TEMPORARY TABLE `tmpOrderMoneyTotal`(`preorderid` int default NULL, `MoneyTotal` float default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;
OPEN v_c;
READLOOP: LOOP
FETCH v_c INTO v_listpinIDs, v_orderid;
IF v_done THEN
LEAVE READLOOP;
ELSE
SET @sqlStr = CONCAT('SELECT SUM(Price) INTO @MoneyTotal FROM ps_payoo_cards Card WHERE payoo_card_id IN (', v_listpinIDs, ')');
#SELECT @sqlStr;
#SELECT v_listpinIDs;
PREPARE stmt FROM @sqlStr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
INSERT INTO tmpOrderMoneyTotal(preorderid, MoneyTotal)
SELECT v_orderid, @MoneyTotal;
END IF;
END LOOP;
CLOSE v_c;

SELECT * FROM tmpOrderMoneyTotal;
END //
delimiter;

#CALL uspRetrieveData();

Tks so much