Geek Answers Handbook

SQL syntax error when creating stored procedure in MYSQL

I find it hard to find an error when trying to create a stored procedure in mysql.

if I run each individual line of the procedure independently, everything works 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 (useful) error: ERROR 1064 (42000): you have an error in the SQL syntax; check the manual that matches your MySQL server version for the correct syntax to use next to '' on line 3 ... I just don't know where to start debugging, since each of these lines is correct.

any advice?

+6
mysql stored-procedures
source share
3 answers

Since line 3 contains the first ; maybe 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;
+21
source share

You never declare your @child_left variable.

+1
source share

Thanks, nearby '' on line 3 was my problem, and the separator instruction fixed it! I always want everything to make sense, and it happens. Since `` indicates this at the end of the procedure, but the END statement was not found, therefore, a syntax error. And I wondered why I continued to see a lot of people using the delimiter expression. I see the light!

+1
source share

More articles:


All Articles