Ninja Ninja - 6 months ago 16
MySQL Question

Stuck in creating a Simple procedure in MySQL [IF statement]

I want to create a procedure in MySql using IF statement that would let me show the names of all students who's average is above 70 when I give the id of a student as IN parameter.

Is it possible to give many student Ids at a time and access more results using CALL and SELECT?

Student Table:


id, name, major, avg, status

'33', 'Vera Violet', NULL, '92',
'UNCL'

'94', 'Pat Pink', 'POLS', '93', 'UNCL'

'101', 'Bob Blue', 'ECON', '73', 'UNCL'

'102', 'Wyatt White', 'GEOG', '64', 'UNGR'

'302', 'Yolanda Yellow', 'HIST', '64', 'UNGR'

'403', 'Bryan Black',
'ECON', '87', 'GRAD'

'576', 'Ruby Rose', 'ECON', '77', 'UNCL'

'992', 'Greg Green', 'COMM', '86', 'GRAD'


Here is what I tried: (But didn't worked)

DELIMITER $$
CREATE PROCEDURE sort_Students_By_Avg( IN student_Id INT(11), OUT student_Name VARCHAR(20))
BEGIN
DECLARE student_Avg INT(11);
SELECT avg INTO student_Avg
FROM student
WHERE id=student_Id;

IF (student_Avg > 70)
THEN SET student_Name = student.name;
END IF;
END $$

Answer

This doesn't seem particularly useful, but a procedure would look like this:

CREATE PROCEDURE sort_Students_By_Avg (
    IN in_student_Id INT(11),
    OUT out_student_Name VARCHAR(20)
)
BEGIN
    SELECT s.name INTO out_student_name
    FROM student s
    WHERE s.id = in_student_Id AND s.avg > 70; 
END;
Comments