MySQL Error 1064: You have an error in your SQL syntax

Question

    DELIMITER //
    CREATE FUNCTION fnc_credit_custstatus
    RETURNS VARCHAR(6) DETERMINISTIC
     BEGIN
     DECLARE custstatus VARCHAR(6);
     IF CustCredit>='1000',THEN SET custstatus='VIP';
     ELSEIF CustCredit<'1000',THEN SET custstatus='NONVIP';
   END IF;
   RETURN (custstatus);
   END//
   DELIMITER ;

Query:

CREATE function fnc_credit_custstatus returns varchar(6) deterministic 
begin DECLARE custstatus VARCHAR(6); if CustCredit>='1000...

Error Code: 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 'varchar(6) deterministic begin DECLARE custstatus VARCHAR(6); if CustCredit>=' at line 2

Liggins

try renove the comma after the if condition IF CustCredit>='1000' THEN – Pehlevi

DELIMITER // CREATE FUNCTION fnc_credit_custstatus RETURNS VARCHAR(6) DETERMINISTIC BEGIN DECLARE custstatus VARCHAR(6); IF CustCredit>=1000,THEN SET custstatus='VIP'; ELSEIF CustCredit<1000,THEN SET custstatus='NONVIP'; END IF; RETURN custstatus; END // DELIMITER ; – Liggins

I tried, but it still wrong – Liggins

IF CustCredit>=1000,THEN SET ??? you have not removed any comma you have also comma after the endif – Pehlevi

Why are you testing custcredit agains a string - is custcredit a string also? (not directly related to your problem) – Pomiculture

Answer 1

Try remove comma

    DELIMITER //
        CREATE FUNCTION fnc_credit_custstatus
        RETURNS VARCHAR(6) DETERMINISTIC
         BEGIN
         DECLARE custstatus VARCHAR(6);
         IF CustCredit>=1000 THEN SET custstatus='VIP';
         ELSEIF CustCredit<1000 THEN SET custstatus='NONVIP';
       END IF;
       RETURN (custstatus);
       END//
       DELIMITER ;

(and use proper comparision data type)

Pehlevi

It doesn't show my Error, but why it doesn't show that I created FUNCTION succeeded？ – Liggins

Answer 2

I got the same error below:

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 'DETERMINISTIC

When I tried to create addition() function without return value type as shown below:

DELIMITER $$
                                    -- Here
CREATE FUNCTION addition(value INT) 
DETERMINISTIC
BEGIN
UPDATE test SET num = num + value;
SELECT num INTO value FROM test;
RETURN value;
END$$ 

DELIMITER ;

So, I set return value type to addition() function as shown below, then I could create addition() function without error:

DELIMITER $$
                                    -- Here
CREATE FUNCTION addition(value INT) RETURNS INT
DETERMINISTIC
BEGIN
UPDATE test SET num = num + value;
SELECT num INTO value FROM test;
RETURN value;
END$$ 

DELIMITER ;

Orlon