Reputation: 67
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
Upvotes: 0
Views: 2632
Reputation: 44844
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; $$
Upvotes: 1