#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    2
    Rep Power
    0

    [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
  2. #2
  3. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,147
    Rep Power
    1316
    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.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    2
    Rep Power
    0
    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

IMN logo majestic logo threadwatch logo seochat tools logo