Variables in procedures - MySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Pefo
Forum Newbie
Posts: 9
Joined: Wed Dec 16, 2009 5:10 pm

Variables in procedures - MySQL

Post 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.
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: Variables in procedures - MySQL

Post 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)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Variables in procedures - MySQL

Post 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
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
stevenrokz
Forum Newbie
Posts: 2
Joined: Thu Jan 28, 2010 5:24 am

Re: Variables in procedures - MySQL

Post by stevenrokz »

This is general SOP, any name (variable, Table name) which contradicts with reserved words or keys can result in errors like this.
Pefo
Forum Newbie
Posts: 9
Joined: Wed Dec 16, 2009 5:10 pm

Re: Variables in procedures - MySQL

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Variables in procedures - MySQL

Post by VladSun »

Yes, you can by using prepared statements as shown in the examples I gave you.
There are 10 types of people in this world, those who understand binary and those who don't
Pefo
Forum Newbie
Posts: 9
Joined: Wed Dec 16, 2009 5:10 pm

Re: Variables in procedures - MySQL

Post by Pefo »

Thanks 'all.
I have some reading to do.
Might be back with an acing forehead...
Post Reply