If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > Database Server Software > MySQL > Please help With this procedure with cursor

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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
Reply With Quote
Reply

Tags
cursor, error 1329, mysql, procedures

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On