creating mysql function using phpmyadmin

Question

I want to enter the following function to count a number of finished tasks from a mysql table in phpmyadmin, but its always returning a none descriptive error:

 DELIMITER $$
 CREATE FUNCTION `num_completed`(v1 INT)
 RETURNS INT
 BEGIN
 DECLARE icm INT;
 SELECT SUM(IF(completed='yes',1,0)) AS completed INTO icm FROM ri_t_course_progress WHERE enrollment_id=v1;
 RETURN icm;
 END$$

The query itself should be correct. I've tested it and returns the desired result. Anybody know whats wrong?

• madhu131313: I tried this on my phpmyadmin but I am getting this error #1415 - Not allowed to return a result set from a function
• jcho360: try placing ICM OUT in the first line create function ....
• Joni: What error do you get? I pasted the SQL into mysql and it created the function with no errors.

Answer 1

You need to assign the result of the SELECT into the variable. Here's one way:

 SELECT SUM(IF(completed='yes',1,0)) INTO icm 
 FROM ri_t_view_course_progress WHERE enrollment_id=v1;

• Amelia: You're right. I missed that completely. However... It still gives none descriptive error message when I enter it into mysql like so: DELIMITER $$ CREATE FUNCTION num_completed(v1 INT) RETURNS INT BEGIN DECLARE icm INT; SELECT SUM(IF(completed='yes',1,0)) INTO icm FROM ri_t_course_progress WHERE enrollment_id=v1; RETURN icm; END$$