Alex Rivera | Logout

SQL syntax error when creating a stored procedure in MySQL

Asked 2009-03-12T15:14:06.823
14

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.

Do you have any tips?

Edit
Report

1 Answer

4

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!

answered 2009-04-02T01:01:19.773

Your Answer