MySQL User defined function - an error in your SQL syntax

Question

I tried with various version but I am getting the same error message while creating user define functions like this

CREATE FUNCTION avg_month(shop INT) RETURNS DECIMAL
BEGIN
DECLARE
    avg_m_sales DECIMAL;
SET
    avg_m_sales = 1000; 
RETURN avg_m_sales;
END;

The error message is:

Error SQL query:

CREATE FUNCTION avg_month(shop INT) RETURNS DECIMAL
BEGIN
DECLARE
    avg_m_sales DECIMAL

MySQL said: Documentation

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 4

Sanjay · Accepted Answer · 2019-01-26 11:04:31Z

1

Delimiters other than the default ; are typically used when defining functions, stored procedures, and triggers wherein you must define multiple statements.

Try to create below way. "testdb" is you database name.

DELIMITER $$

CREATE

    FUNCTION `testdb`.`avg_month`(shop INT)
    RETURNS DECIMAL

    BEGIN
DECLARE
    avg_m_sales DECIMAL;
SET
    avg_m_sales = 1000; 
RETURN avg_m_sales;
    END$$

DELIMITER ;

edited Jan 26, 2019 at 11:04

answered Jan 26, 2019 at 5:28

Sanjay

2,5131 gold badge16 silver badges29 bronze badges

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

1 Comment

P.Salmon Over a year ago

some explanation of why this works would help anyone else visiting this question.

Collectives™ on Stack Overflow

MySQL User defined function - an error in your SQL syntax

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