MySQL Syntax error while creating stored function

Question

I am getting a syntax error on executing the below function in MySQL.

CREATE FUNCTION FABC (P_MEMBER_EMAIL VARCHAR(30))
RETURNS int 
BEGIN
DECLARE RESULT int;
DECLARE V_DATE DATE;
SELECT DOJ+365 INTO V_DATE FROM CHYK_MEMBER_MASTER
WHERE UPPER(MEMBER_EMAIL)=UPPER(P_MEMBER_EMAIL) AND SUBSCRIPTION='Y';
IF V_DATE>SYSDATE THEN
SET RESULT=1; 
ELSE
SET RESULT=0; 
END IF;
RETURN RESULT;
END;

The error is as follows "#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 4

Not sure what's causing this. Can anyone help me ?

Peter · Accepted Answer · 2017-02-20 15:35:05Z

2

You need to set a delimiter that will tell MySQL the END of the function definition.

DELIMITER $$
CREATE FUNCTION `FABC`(`P_MEMBER_EMAIL` VARCHAR(30)) RETURNS int(11)
BEGIN
DECLARE RESULT int;
DECLARE V_DATE DATE;
SELECT DOJ+365 INTO V_DATE FROM CHYK_MEMBER_MASTER
WHERE UPPER(MEMBER_EMAIL)=UPPER(P_MEMBER_EMAIL) AND SUBSCRIPTION='Y';
IF V_DATE>SYSDATE THEN
SET RESULT=1; 
ELSE
SET RESULT=0; 
END IF;
RETURN RESULT;
END$$
DELIMITER ;

answered Feb 20, 2017 at 15:35

Peter

6,6994 gold badges33 silver badges36 bronze badges

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

Collectives™ on Stack Overflow

MySQL Syntax error while creating stored function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related