MySQL Forums
Forum List  »  Views

Store Procedure: last record repeat 2 times (i use cursor)
Posted by: Sandip Kapadiya
Date: March 28, 2011 06:24AM

Hi friends can anyone tell me how this happens?

-> Store Procedure: last record repeat 2 times
--> i create a view and use cursor

Store Procedure Code:
--- *****
SET @degreeID = '(1,6,7)';
DROP VIEW IF EXISTS studentList;

SET @sqlView = concat('CREATE VIEW studentList As SELECT s.StudentID, s.DisplayName, s.DegreeID sDegreeID, s.SessionNo, d.Name as DegreeName
FROM tblstudentmst AS s LEFT JOIN tbldegreemst AS d ON s.DegreeID = d.DegreeID
WHERE s.SessionNo=',@sessID,' AND s.BatchYear="',@batchY,'" AND s.DegreeID IN (', @degreeID,
') ORDER BY s.DegreeID, s.LastName, s.FirstName, s.MiddleName ');
PREPARE showview FROM @sqlView;
EXECUTE showview;
END IF;

SET x = @degreeCNT;
SELECT x;
#SET @n = 0;
WHILE x > 0 DO
SET di = SUBSTRING_INDEX(SUBSTRING_INDEX(@degreeID,',',-x),',',1);
SELECT di;
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE iStudentID INT(11);
DECLARE dID INT(11);
DECLARE sID INT(11);
DECLARE dName VARCHAR(15);
DECLARE vStudentCode VARCHAR(20);
DECLARE vName VARCHAR(50);

DECLARE stdet CURSOR FOR SELECT * FROM studentList WHERE sDegreeID=di;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done =1;

OPEN stdet;
SET @n = 0;

REPEAT
FETCH stdet INTO iStudentID, vName, dID, sID, dName;
SET @vDegreeName := '';
SET @sqlselect := '';

SET @num = func_get_stuCode(3,'0',@n);

SET @seatNo = concat(@eSeat, dName, @num);
# Generate Student Seat No and Insert in tblexamseatno
SELECT iStudentID,@n,@examCode,@degreeName,@seatNo;

SET @sqlIns = concat('INSERT INTO tblexamseatno (ExamSeatNoID, ExamscheduleID, StudentID, ExamCode,SeatNo)
VALUES ("",',@eID,',',iStudentID,',',@examCode,',"',@seatNo,'") ON DUPLICATE KEY UPDATE
ExamscheduleID= VALUES(ExamscheduleID), StudentID= VALUES(StudentID), ExamCode= VALUES(ExamCode), SeatNo= VALUES(SeatNo)');
#SELECT @sqlIns;
PREPARE ps FROM @sqlIns;
EXECUTE ps;
DROP PREPARE ps;

SET @n = @n + 1;

UNTIL done END REPEAT;
END;
SET x = x - 1;
END WHILE;
--- *****

I print array that display below Result:
--> you can see that iStudentID = 53, 60, 42 repeat 2 times for degree 1, 6, 7
Array
(
[] => Array
(
[0] => Array
(
[x] => 3
)

[1] => Array
(
[di] => 1
)

[2] => Array
(
[iStudentID] => 70
[@n] => 0
[@examCode] => (SELECT ExamCode FROM tblexamschedule WHERE examscheduleID=1)
[@degreeName] => BE(EC),BE(MECH),BE(IT)
[@seatNo] => 111BE(EC)001
)

[3] => Array
(
[iStudentID] => 44
[@n] => 1
[@examCode] => (SELECT ExamCode FROM tblexamschedule WHERE examscheduleID=1)
[@degreeName] => BE(EC),BE(MECH),BE(IT)
[@seatNo] => 111BE(EC)002
)

[4] => Array
(
[iStudentID] => 54
[@n] => 2
[@examCode] => (SELECT ExamCode FROM tblexamschedule WHERE examscheduleID=1)
[@degreeName] => BE(EC),BE(MECH),BE(IT)
[@seatNo] => 111BE(EC)003
)

[5] => Array
(
[iStudentID] => 53
[@n] => 3
[@examCode] => (SELECT ExamCode FROM tblexamschedule WHERE examscheduleID=1)
[@degreeName] => BE(EC),BE(MECH),BE(IT)
[@seatNo] => 111BE(EC)004
)

[6] => Array
(
[iStudentID] => 53
[@n] => 4
[@examCode] => (SELECT ExamCode FROM tblexamschedule WHERE examscheduleID=1)
[@degreeName] => BE(EC),BE(MECH),BE(IT)
[@seatNo] => 111BE(EC)005
)

[7] => Array
(
[di] => 6
)

[8] => Array
(
[iStudentID] => 33
[@n] => 0
[@examCode] => (SELECT ExamCode FROM tblexamschedule WHERE examscheduleID=1)
[@degreeName] => BE(EC),BE(MECH),BE(IT)
[@seatNo] => 111BE(MECH)001
)

[9] => Array
(
[iStudentID] => 56
[@n] => 1
[@examCode] => (SELECT ExamCode FROM tblexamschedule WHERE examscheduleID=1)
[@degreeName] => BE(EC),BE(MECH),BE(IT)
[@seatNo] => 111BE(MECH)002
)

[10] => Array
(
[iStudentID] => 58
[@n] => 2
[@examCode] => (SELECT ExamCode FROM tblexamschedule WHERE examscheduleID=1)
[@degreeName] => BE(EC),BE(MECH),BE(IT)
[@seatNo] => 111BE(MECH)003
)

[11] => Array
(
[iStudentID] => 65
[@n] => 3
[@examCode] => (SELECT ExamCode FROM tblexamschedule WHERE examscheduleID=1)
[@degreeName] => BE(EC),BE(MECH),BE(IT)
[@seatNo] => 111BE(MECH)004
)

[12] => Array
(
[iStudentID] => 60
[@n] => 4
[@examCode] => (SELECT ExamCode FROM tblexamschedule WHERE examscheduleID=1)
[@degreeName] => BE(EC),BE(MECH),BE(IT)
[@seatNo] => 111BE(MECH)005
)

[13] => Array
(
[iStudentID] => 60
[@n] => 5
[@examCode] => (SELECT ExamCode FROM tblexamschedule WHERE examscheduleID=1)
[@degreeName] => BE(EC),BE(MECH),BE(IT)
[@seatNo] => 111BE(MECH)006
)

[14] => Array
(
[di] => 7
)

[15] => Array
(
[iStudentID] => 38
[@n] => 0
[@examCode] => (SELECT ExamCode FROM tblexamschedule WHERE examscheduleID=1)
[@degreeName] => BE(EC),BE(MECH),BE(IT)
[@seatNo] => 111BE(IT)001
)

[16] => Array
(
[iStudentID] => 31
[@n] => 1
[@examCode] => (SELECT ExamCode FROM tblexamschedule WHERE examscheduleID=1)
[@degreeName] => BE(EC),BE(MECH),BE(IT)
[@seatNo] => 111BE(IT)002
)

[17] => Array
(
[iStudentID] => 67
[@n] => 2
[@examCode] => (SELECT ExamCode FROM tblexamschedule WHERE examscheduleID=1)
[@degreeName] => BE(EC),BE(MECH),BE(IT)
[@seatNo] => 111BE(IT)003
)

[18] => Array
(
[iStudentID] => 42
[@n] => 3
[@examCode] => (SELECT ExamCode FROM tblexamschedule WHERE examscheduleID=1)
[@degreeName] => BE(EC),BE(MECH),BE(IT)
[@seatNo] => 111BE(IT)004
)

[19] => Array
(
[iStudentID] => 42
[@n] => 4
[@examCode] => (SELECT ExamCode FROM tblexamschedule WHERE examscheduleID=1)
[@degreeName] => BE(EC),BE(MECH),BE(IT)
[@seatNo] => 111BE(IT)005
)

)

*****************
please tell me why this happens?

Options: ReplyQuote


Subject
Views
Written By
Posted
Store Procedure: last record repeat 2 times (i use cursor)
3801
March 28, 2011 06:24AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.