sqlmysqldatabasemysql-error-1064mysql-function

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


    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


Solution

  • 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)