SQL syntax error when creating a stored procedure in MYSQL

Question

hi all,

i have a hard time locating an error when trying to create a stored procedure in mysql.

if i run every single line of the procedure independently, everything works just fine.

CREATE PROCEDURE cms_proc_add_child (param_parent_id INT, param_name CHAR(255), param_content_type CHAR(255))
BEGIN
    SELECT @child_left := rgt FROM cms_tree WHERE id = param_parent_id;
    UPDATE cms_tree SET rgt = rgt+2 WHERE rgt >= @child_left;
    UPDATE cms_tree SET lft = lft+2 WHERE lft >= @child_left;
    INSERT INTO cms_tree (name, lft, rgt, content_type) VALUES (param_name, @child_left, @child_left+1, param_content_type);
END

i get the following (helpful) error: ERROR 1064 (42000): 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 … i just don't know where to start debugging, as every single one of these lines is correct.

any tips?

Answer 1

You never declare your @child_left variable.

Answer 2

As line 3 contains the first ; perhaps you have a problem with your delimiters.

see http://dev.mysql.com/doc/refman/5.0/en/stored-programs-defining.html

mysql> delimiter //

mysql> CREATE PROCEDURE dorepeat(p1 INT)
    -> BEGIN
    ->   SET @x = 0;
    ->   REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

Answer 3

Thanks, near '' at line 3 was my problem and the delimiter statement fixed it! I always want things to make sense and this does. As the '' indicates it's at the end of the procedure, but no END statement was found thus the syntax error. And I wondered why I kept seeing a lot of people using the delimiter statement. I see the light!

Answer 4

I HVE THE SAME PROB AND THE DELIMITER DOES NOT HELP ME TO SOLVE THS PROB... ANY SUGGESTION..

MY CODE:

create procedure view_user() BEGIN Select * from userLogin; END

---> near '' at line 3 was my problem