MySQL ERROR 1064 in CREATE FUNCTION

Question

I am unable to debug the code for CREATE FUNCTION. It gives the following error:

ERROR 1064 (42000): 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 16

This is the code.

DELIMITER $$
CREATE FUNCTION CHECK_STATUS(SAL FLOAT) RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
    DECLARE STATUS VARCHAR(10);
    SET STATUS = "";
    IF (SAL > 70000) THEN
    SET STATUS = "A CLASS";
    ELSEIF (SAL > 50000) THEN
    SET STATUS = "B CLASS";
    ELSE IF (SAL > 30000) THEN 
    SET STATUS = "C CLASS";
    ELSE
    SET STATUS = "D CLASS";
    END IF;
RETURN STATUS; # This is line 16
END; 
$$

PS: I have seen other question with same title but they were about the DELIMITER so they didn't help. My MySQL version is 5.7.19

Well from what I have read it might be that your last two lines should become END$$ and then a new line to reset the DELIMITER back to ; DELIMITER ; - This is not tested. — TimBrownlaw
– TimBrownlaw, Commented Oct 29, 2017 at 12:57

Michael · Accepted Answer · 2017-10-29 13:48:04Z

2

It will work if you change ELSE IF to ELSEIF. It is treated as ELSE and then unclosed IF clause, without END IF.

edited Oct 29, 2017 at 13:48

answered Oct 29, 2017 at 12:58

Michael

5,3452 gold badges16 silver badges37 bronze badges

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

3 Comments

TimBrownlaw Over a year ago

Just saw that there is one of each of the above in the OP's example.

Nikhil Wagh Over a year ago

Ok that worked. But then why It said error in line 16, why not error near ELSE IF something like that. @Michael O.

Michael Over a year ago

@NikhilWagh Because you've had an unclosed IF clause (part of ELSE IF) till the end of the function.

Collectives™ on Stack Overflow

MySQL ERROR 1064 in CREATE FUNCTION

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related