MySQL - Correct Syntax for MySQL user defined function

Question

I have created the following function but seem to be getting an error.

DELIMITER $$
CREATE FUNCTION avg_month(shop INTEGER) RETURNS DECIMAL
BEGIN
  DECLARE
    avg_m_sales DECIMAL
  SELECT TRUNCATE((COUNT(paymentdate) / 12),2) FROM sales_table 
  WHERE YEAR(paymentdate) = 2017 AND shopid = shop
RETURN avg_m_sales
END $$
DELIMITER ;

I am getting the following error.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT TRUNCATE((COUNT(payid) / 12),2) FROM fss_Payment WHERE YEAR(paydate) ' at line 5

I also tried adding ; to the end of the statements but when I do this i get the following error:

#1415 - Not allowed to return a result set from a function

@tadman Tried that but then get the following error #1415 - Not allowed to return a result set from a function — HoldTight
– HoldTight, Commented Nov 24, 2017 at 21:13
Why are you declaring avg_m_sales and returning it, but not actually assigning its value in the SELECT statement? (I don't know MySQL functions, but my guess would be that without the assignment to a variable, MySQL thinks you want to return the resultset of SELECT from the function, as would happen in a stored procedure.) — Matt Gibson
– Matt Gibson, Commented Nov 24, 2017 at 21:17
Try SET avg_m_sales = (SELECT ... );. Or just skip that avg_m_sales variable - RETURN (SELECT ... ) might also work. — Paul Spiegel
– Paul Spiegel, Commented Nov 24, 2017 at 21:19

Ahmed Taha · Accepted Answer · 2017-11-24 21:37:42Z

1

because you didn't set a value for avg_m_sales
line number 4

     CREATE FUNCTION avg_month(shop INTEGER) RETURNS DECIMAL 
     BEGIN 
       DECLARE avg_m_sales DECIMAL;
         SET avg_m_sales=(
         SELECT TRUNCATE((COUNT(paymentdate) / 12),2) FROM sales_table
         WHERE YEAR(paymentdate) = 2017 AND shopid = shop);
       RETURN avg_m_sales;
     END;

EDIT : another answer

     CREATE FUNCTION avg_month(shop INTEGER) RETURNS DECIMAL 
     BEGIN 
         RETURN (SELECT TRUNCATE((COUNT(paymentdate) / 12),2) FROM sales_table
         WHERE YEAR(paymentdate) = 2017 AND shopid = shop);
     END;

edited Nov 24, 2017 at 21:37

answered Nov 24, 2017 at 21:27

Ahmed Taha

1472 silver badges8 bronze badges

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

8 Comments

Matt Gibson Over a year ago

You should be able to SELECT directly into the variable, e.g.

SELECT avg_m_sales = TRUNCATE((COUNT(paymentdate) / 12),2) FROM sales_table WHERE YEAR(paymentdate) = 2017 AND shopid = shop

without the SET and the subquery, I think.

Ahmed Taha Over a year ago

i tried without set and it returns the same error Not allowed to return a result set from a function

Paul Spiegel Over a year ago

@MattGibson At least in MySql the = in SELECT avg_m_sales = .. is a boolean operator. What might work is SELECT .. INTO (avg_m_sales) FROM ...

Ahmed Taha Over a year ago

he has to set return before select if he don't want to use set

HoldTight Over a year ago

@AhmedTaha shop is the variable being passed in and compared to shopid which is from my table.

|

Collectives™ on Stack Overflow

MySQL - Correct Syntax for MySQL user defined function

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related