Getting a MYSQL 1064 error when creating a function

Question

DELIMITER $$

    CREATE FUNCTION nameOfFunct(intIn int)
    RETURN int
    BEGIN
        DECLARE intOut INT;
        SET intOut = SELECT count(*)
            FROM tableToTakeFrom
            WHERE columToCompareTo = intIn;

        RETURN intOut;
    END;
        $$

DELIMITER;

If I try to run this all I get is:

SQL Error (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 'RETURN int
BEGIN
DECLARE intOut INT;
SET intOut = select count(' at line 2

wchiquito · Accepted Answer · 2013-11-02 12:50:23Z

2

A couple of changes to resolve the problem:

DELIMITER $$

CREATE FUNCTION nameOfFunct(intIn INT)
-- RETURN INT
RETURNS INT
BEGIN
    DECLARE intOut INT;
    /*SET intOut = SELECT COUNT(*)
        FROM tableToTakeFrom
        WHERE columToCompareTo = intIn;*/
    SET intOut = (SELECT COUNT(*)
        FROM tableToTakeFrom
        WHERE columToCompareTo = intIn);
    RETURN intOut;
END $$

DELIMITER ;

answered Nov 2, 2013 at 12:50

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

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

Comments

Mureinik · Accepted Answer · 2013-11-02 12:46:34Z

1

Defining the return type is done by the RETURNS keywrod, not the RETURN keyword:

CREATE FUNCTION nameOfFunct(intIn int)
RETURNS int
BEGIN
    DECLARE intOut INT;
    SET intOut = SELECT count(*) 
                 FROM   tableToTakeFrom
                 WHERE  columToCompareTo = intIn;
    RETURN intOut;
END;

answered Nov 2, 2013 at 12:46

Mureinik

316k54 gold badges399 silver badges405 bronze badges

Collectives™ on Stack Overflow

Getting a MYSQL 1064 error when creating a function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related