yes
2011/10/4 Paul Nickerson <paul.nicker...@escapemg.com>
> You need a space before the word VALUES
>
> ------------------------------
> *From: *"Adam Gerson" <agers...@cgps.org>
> *To: *"luiz rodrigo mottin" <luizrodrigomot...@gmail.com>
> *Cc: *mysql@lists.mysql.com
> *Sent: *Tuesday, October 4, 2011 6:00:24 PM
> *Subject: *Re: Variables in stored procedure
>
>
> Thanks Luiz,
>
> That got me closer. I was able to save the stored proc. It should be
> execute stm; not execute @sql; right?
>
> I get this when I try to execute it:
> 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 ''309',
> '0', 'statpress_mincap', 'edit_posts', 'yes'), ('306', '0',
> 'statpress_co' at line 1
>
>
> begin
>
> declare v_max int unsigned default 21;
> declare v_counter int unsigned default 21;
> declare TABLENAME text;
>
> start transaction;
> while v_counter <= v_max do
> SET TABLENAME = CONCAT('wp_',v_counter, '_options');
> set @sql = concat( "INSERT INTO ", TABLENAME, "VALUES ('309', '0',
> 'statpress_mincap', 'edit_posts', 'yes'), ('306', '0',
> 'statpress_collectloggeduser', 'checked', 'yes'), ('307', '0',
> 'statpress_autodelete', '1 year', 'yes'), ('308', '0',
> 'statpress_daysinoverviewgraph', '31', 'yes'), ('310', '0',
> 'statpress_donotcollectspider', 'checked', 'yes'), ('311', '0',
> 'statpress_autodelete_spider', '1 day', 'yes'), ('312', '0',
> 'statpress_number_display_post_and_page', '20', 'yes'), ('313', '0',
> 'statpress_number_display_ip_spy_visitor', '20', 'yes'), ('314', '0',
> 'statpress_number-display_visit_spy_visitor', '20', 'yes');");
> prepare stm from @sql;
> execute stm;
> set v_counter=v_counter+1;
> end while;
> commit;
> END
>
>
> --
> Adam Gerson
> Assistant Director of Technology
> Columbia Grammar and Prep School
> phone. 212-749-6200 ex. 321
> fax. 212-428-6806
> ager...@cgps.org
> http://www.cgps.org
>
> On 10/4/11 5:29 PM, luiz rodrigo mottin wrote:
> > you can use:
> > set @sql = concat( "INSERT INTO ", TABLENAME, "VALUES ('309', '0',
> > 'statpress_mincap', 'edit_posts', 'yes')");
> > prepare stm from @sql;
> > execute @sql;
> >
> > 2011/10/4 Adam Gerson <agers...@cgps.org <mailto:agers...@cgps.org>>
> >
> > I am getting the error that "TABLENAME" does not exist. How do I get
> > it to substitute the value stored in TABLENAME, and not the literal
> > string?
> >
> > begin
> >
> > declare v_max int unsigned default 1;
> > declare v_counter int unsigned default 21;
> > declare TABLENAME text;
> >
> > start transaction;
> > while v_counter <= v_max do
> > SET TABLENAME = CONCAT('wp_',v_counter, '_options');
> > INSERT INTO TABLENAME VALUES ('309', '0',
> > 'statpress_mincap', 'edit_posts', 'yes');
> > set v_counter=v_counter+1;
> > end while;
> > commit;
> > END
> >
> >
> > Thanks,
> > Adam
> >
> > --
> > Adam Gerson
> > Co-Director of Technology
> > Columbia Grammar and Prep School
> > phone. 212-749-6200 <tel:212-749-6200> ex. 321
> > fax. 212-428-6806 <tel:212-428-6806>
> > ager...@cgps.org <mailto:ager...@cgps.org>
> > http://www.cgps.org
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/mysql?__unsub=luizrodrigomottin@gmail.__com
> > <http://lists.mysql.com/mysql?unsub=luizrodrigomot...@gmail.com>
> >
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=paul.nicker...@grooveshark.com
>
>
>
