Page 1 of 1

Variables in procedures - MySQL

Posted: Wed Jan 27, 2010 2:55 pm
by Pefo
How come all my variables but 'mytable' is working?

Code: Select all

DELIMITER $$
CREATE PROCEDURE add_child2 (IN mytable CHAR(40), IN myparent CHAR(40), IN mynew CHAR(40))
BEGIN
    DECLARE myLeft INT;
    SELECT lft INTO myLeft FROM mytable WHERE name = myparent;
    UPDATE mytable SET rgt = rgt + 2 WHERE rgt > myLeft;
    UPDATE mytable SET lft = lft + 2 WHERE lft > myLeft;
    INSERT INTO mytable(name, lft, rgt) VALUES(mynew, myLeft + 1, myLeft + 2);
 
END$$
DELIMITER ;
 
Errormessage: table mytable does not exist.
My passed tablename does not appear.

Re: Variables in procedures - MySQL

Posted: Wed Jan 27, 2010 7:10 pm
by JakeJ
Has the table been created? Does it have the correct data types? Is mytable in a different database perhaps? Try appending the database name to my table, (ex: databasename.mytable)

Re: Variables in procedures - MySQL

Posted: Thu Jan 28, 2010 1:47 am
by VladSun
You can't use variables for table and column names. You need a dynamic query - take a look at the last example in http://dev.mysql.com/tech-resources/art ... dproc.html

Re: Variables in procedures - MySQL

Posted: Thu Jan 28, 2010 5:31 am
by stevenrokz
This is general SOP, any name (variable, Table name) which contradicts with reserved words or keys can result in errors like this.

Re: Variables in procedures - MySQL

Posted: Sat Jan 30, 2010 2:07 pm
by Pefo
Thanks.
Been reading and it looks like I´m able to give a tablename through an @-variable.
Right?
It is not really nesessary in this case, but I like to get a hold of the structur of it.

Re: Variables in procedures - MySQL

Posted: Sat Jan 30, 2010 2:47 pm
by VladSun
Yes, you can by using prepared statements as shown in the examples I gave you.

Re: Variables in procedures - MySQL

Posted: Sun Jan 31, 2010 11:39 am
by Pefo
Thanks 'all.
I have some reading to do.
Might be back with an acing forehead...