Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2012
    Posts
    1

    Red face Please help With this procedure with cursor

    Hello All,

    Please do help me with the following procedure i am getting the following Warning
    Warning Code : 1329
    No data - zero rows fetched, selected, or processed

    Procedure ::


    DELIMITER $$

    USE `ProcedureTest`$$

    DROP PROCEDURE IF EXISTS `test`$$

    CREATE PROCEDURE `test`(IN newloginId VARCHAR(40),OUT msg VARCHAR(75), OUT total INT(3))
    BEGIN

    DECLARE d INT DEFAULT 0;
    DECLARE isExistedAccId INT DEFAULT 0;
    DECLARE a INT;

    DECLARE p_AccId INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE accCount INT(10);
    DECLARE cur CURSOR FOR SELECT AccId FROM Table2 ORDER BY AccId;

    SELECT COUNT(AccId) INTO accCount FROM Table2 ORDER BY AccId;


    OPEN cur;


    WHILE(accCount > 0)
    DO

    FETCH cur INTO p_AccId;

    SET isExistedAccId = 0;




    SELECT AccountId INTO isExistedAccId FROM Table1 WHERE AccountId = p_AccId AND TeamMemberId = newloginId LIMIT 1;


    IF isExistedAccId = 0 THEN
    INSERT INTO Table1(AccountId, TeamMemberId, SalesFlag, PrimaryFlag) VALUES(p_AccId,newloginId,1,0);
    SET isExistedAccId = 0;
    SET i = i + 1;
    END IF;
    SET accCount = accCount - 1;
    END WHILE;
    CLOSE cur;

    SET msg = 'rows Inserted';
    SET total = i;
    END$$

    DELIMITER ;

    Thanks in advance,

    Abhi

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    860
    You forgot to add an exception handler for the FETCH once we have reached the last record. Check this out MySQL – merging external data IT Integrated Business Solutions
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •