mysql error (#1064) stored procedure creation error

Question

I'm trying to create a stored procedure but i keep getting 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 'NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' B' at line 3

My SQL is as follows:

DELIMITER $$
CREATE FUNCTION `prodcat_descendants` (idcat INTEGER(11) )
    RETURNS VARCHAR
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
   RETURN (select GROUP_CONCAT(DISTINCT id_category SEPARATOR ',') from cms_prodcategories where id_parent=idcat and active='1');
END$$

I've tried to include DELIMITER but no result. I guess the problem might be in the quotes. Can someone point me out the solution? Thanks!

MySQL version 5.5

Abhik Chakraborty · Accepted Answer · 2014-10-22 14:15:55Z

1

You need a varchar length on the return statement of the procedure something as

RETURNS VARCHAR(1024)

So the procedure becomes

DELIMITER $$
CREATE FUNCTION `prodcat_descendants` (idcat INTEGER(11) )
    RETURNS VARCHAR(1024)
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
   RETURN (select GROUP_CONCAT(DISTINCT id_category SEPARATOR ',') from cms_prodcategories where id_parent=idcat and active='1');
END; $$

answered Oct 22, 2014 at 14:15

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

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

2 Comments

Luis Over a year ago

Thanks. There was a problem with VARCHAR definition but a new error appears: #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 '' at line 9 - used phpmyadmin

Luis Over a year ago

Tried with SQL Manager and it worked but first i needed to set this GLOBAL: SET GLOBAL log_bin_trust_function_creators = 1; Thanks!

Collectives™ on Stack Overflow

mysql error (#1064) stored procedure creation error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related