CODEWITHSUNDEEP
mysql
Ben Jonson
Ben Jonson

Reputation: 739

MySQL User defined function - an error in your SQL syntax

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

Upvotes: 0

Views: 362

Answers (1)

Sanjay
Sanjay

Reputation: 2503

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 ;

Upvotes: 1

Related Questions