CODEWITHSUNDEEP
sqlmysqldatabasemysql-error-1064mysql-function
Guenyeu Tan
Guenyeu Tan

Reputation: 25

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

Upvotes: 1

Views: 1657

Answers (2)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

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 ;

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133400

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)

Upvotes: 0

Related Questions