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

Question

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 $$

Gordon Linoff · Accepted Answer · 2016-05-29 03:11:11Z

1

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;

answered May 29, 2016 at 3:11

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Anonymous Over a year ago

Thanks a lot, buddy. Is it possible to give many student Ids at a time and access more results using CALL and SELECT?

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related