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?

I tried this on my phpmyadmin but I am getting this error #1415 - Not allowed to return a result set from a function — madhu131313
– madhu131313, Commented Feb 7, 2013 at 12:49
try placing ICM OUT in the first line create function .... — jcho360
– jcho360, Commented Feb 7, 2013 at 13:14
What error do you get? I pasted the SQL into mysql and it created the function with no errors. — Joni
– Joni, Commented Feb 7, 2013 at 18:33

Joni · Accepted Answer · 2013-02-07 12:55:16Z

2

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;

answered Feb 7, 2013 at 12:55

Joni

112k14 gold badges151 silver badges201 bronze badges

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

1 Comment

Amelia Over a year ago

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

Collectives™ on Stack Overflow

creating mysql function using phpmyadmin

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