CODEWITHSUNDEEP
mysqlsqlstored-proceduresstored-functions
jcsbrotha
jcsbrotha

Reputation: 99

Can't build MySQL Function, getting Error 1064

I am just starting to try MySQL functions and when trying to build the below function I get an error.

-> DELIMITER //
-> CREATE FUNCTION addD(d VARCHAR(20), rt VARCHAR(40), a VARCHAR(5), de VARCHAR(35))                                                                                        
-> RETURNS VARCHAR(20)
-> BEGIN
->     DECLARE ret VARCHAR(20);
->     IF (SELECT count(DN) FROM DB.TABLE WHERE DN = d) > 0 THEN ret=concat(s,' - exists!');
->     ELSE ret = 'clear';
->     IF ret = 'clear' THEN (INSERT INTO DB.TABLE (DN,ROUTE,ACTIVE,Description) VALUES (dn,rt,a,de));
->     RETURN ret;
-> END //

The error I get is:

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 '= CONCAT(dn,' - exists!'); ELSE ret = 'clear'; IF ret = 'clear' THEN (IN' at line 1

Any help would be great. Please be gentle as I am new to MySQL functions.

Upvotes: 1

Views: 163

Answers (1)

peterm
peterm

Reputation: 92785

Your IF syntax is incorrect and you need to use SET or SELECT to assign a value to a variable.

DELIMITER //
CREATE FUNCTION addD(d VARCHAR(20), rt VARCHAR(40), a VARCHAR(5), de VARCHAR(35))                                                                                        
RETURNS VARCHAR(20)
BEGIN
    DECLARE ret VARCHAR(20);

    IF (SELECT count(DN) FROM DB.TABLE WHERE DN = d) > 0 THEN 
        SET ret = CONCAT(s,' - exists!');
    ELSE 
        SET ret = 'clear';
    END IF;

    IF ret = 'clear' THEN 
        INSERT INTO DB.TABLE (DN,ROUTE,ACTIVE,Description) VALUES (dn,rt,a,de);
    END IF;

    RETURN ret;
END //

Note that in statement

SET ret = CONCAT(s,' - exists!');
                 ^

You're trying to concatenate with non existent variable s;

Also it looks like it is a job for a stored procedure, not a stored function.

Upvotes: 2

Related Questions