Stored Procedure Syntax Error

Question

I'm an admitted newbie with stored procedures. The following is generating a syntax error.

"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 3"

CREATE PROCEDURE get_user_association_list (IN uid INT)
BEGIN
DECLARE rolelist VARCHAR(255);
DECLARE role_id INT;
DECLARE cur1 CURSOR FOR SELECT assoc_type_id FROM cause_users_assoc WHERE user_id = uid;
OPEN cur1;
REPEAT
FETCH cur1 INTO role_id;
SET rolelist = CONCAT(rolelist, role_id);
SET rolelist = CONCAT(rolelist, ',');
UNTIL done END REPEAT;
CLOSE cur1;
RETURN rolelist;
END;

Help please.

Answer 1

It looks like you need to use the DELIMITER command to change the statement delimiter from ; to something else while the procedure is being defined. This enables the ; delimiter used in the procedure body to be passed through to the server rather than being interpreted by MySQL itself.

Therefore you may want to try the following:

DELIMITER $$

CREATE PROCEDURE get_user_association_list (IN uid INT)
BEGIN

-- // Your stored procedure body, using semicolon delimiters

END $$

DELIMITER ;

Further reading:

• MySQL 5.1 Reference Manual: Defining Stored Programs