Re: Student_id and MAX score in one OUT parameter

[B Aneja]

Hi,

Thank you for your response. I understand what you are saying. Unfortunately, I am not sure how to do that. This is my first ever procedure in MySQL and all I know is that there can be IN, OUT and INOUT procedures and that we pass the IN parameters to the Sproc, and the OUT parameter from the Sproc. Until now I didn't think we could assign two values to one OUT parameter. I have attempted to begin writing the procedure below!! I would appreciate if you could help me alter the procedure below.


DELIMITER $



DROP PROCEDURE IF EXISTS MAX_SCORE$



CREATE PROCEDURE MAX (IN start_student_id INT,
IN end_student_id INT,
OUT,
OUT rc INT

)


BEGIN



BEGIN


set rc := -1;



SELECT NAME, MAX(SCORE),MIN(SCORE),AVG(SCORE)
FROM STUDENT, SCORE

WHERE STUDENT.STUDENT_ID = SCORE.STUDENT_ID

AND STUDENT.STUDENT_ID = in_student_id

GROUP BY NAME

;


set rc:=0;


END;


END$




DELIMITER ;



These are things I want in the procedure:

a. The procedure takes TWO input parameters for student Id’s, {start_student_id and end_student_id and it has TWO output parameters}.

b. Identify the student who has received maximum score among these students.

c. The procedure should return the student_id and their MAX score as one OUT parameter

d. The procedure also returns return code indicating success / failure.