[SOLVED] Defining a variable when I define a mysql function

Issue

I get an error when defining the following mysql function:

CREATE DEFINER=`root`@`localhost` FUNCTION PDATE(`gdate` datetime) RETURNS char(100) CHARSET utf8
BEGIN
    DECLARE 
        i,
        gy, gm, gd,
        g_day_no, j_day_no, j_np,
        jy, jm, jd INT DEFAULT 0;
END

The error is:

#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 ” at line 11

Even if I simply define just one variable, I get this error.
What is the problem?

Thanks…

Solution

You code is multi statement so you might need to set delimiters https://dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html and ‘the function body must contain a RETURN value statement’ – https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html

DROP FUNCTION IF EXISTS F;

DELIMITER $$
CREATE FUNCTION F(`gdate` datetime) 
RETURNS char(100) CHARSET utf8
BEGIN
    DECLARE 
        i,
        gy, gm, gd,
        g_day_no, j_day_no, j_np,
        jy, jm, jd 
          INT DEFAULT 0;
     RETURN i;
END $$
DELIMITER ;

https://www.db-fiddle.com/f/8kWMQjBS57kNaAKWq2EZhM/1

Answered By – P.Salmon

Answer Checked By – Willingham (BugsFixing Volunteer)