Hi,

I'm using MySQL 5.1.42. I'm trying to create a function, but running
into some syntax errors taht I can't figure out. The function is


CREATE FUNCTION GET_FINISHES (p_id INT) RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
DECLARE finish_name VARCHAR(255);
DECLARE result VARCHAR(255);
DECLARE cur CURSOR FOR SELECT NAME FROM PROD_PRODUCT_FINISHES P,
FINISH F WHERE P.FINISH_ID = F.ID AND P.PRODUCT_ID = p_id;
OPEN cur;
read_loop: LOOP
FETCH cur INTO finish_name;
IF LENGTH(result) != 0 THEN
SET result = CONCAT(result, ',');
END IF
SET result = CONCAT(result, finish_name);
END LOOP;
CLOSE cur;
RETURN result;
END;

and the error I get is

MySQL said:

#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 4

What thing(s) are wrong in the above function? Thanks, - Dave

On 2/20/2011 3:05 PM, Dave wrote:
How are you creating the function?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On 2011-02-20 21:05, Dave wrote:
Try using another delimiter than ";".

delimiter @
CREATE FUNCTION GET_FINISHES (p_id INT) RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
DECLARE finish_name VARCHAR(255);
[...]
end @
delimiter ;

You might want to have a look the GROUP_CONCAT function:

http://dev.mysql.com/doc/refman/5.0/...functions.html

/Lennart