Re: Variables in stored procedure

[luiz rodrigo mottin]

yes

2011/10/4 Paul Nickerson <paul.nickerson@stripped>

> You need a space before the word VALUES
>
> ------------------------------
> *From: *"Adam Gerson" <agersonl@stripped>
> *To: *"luiz rodrigo mottin" <luizrodrigomottin@stripped>
> *Cc: *mysql@stripped
> *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. [phone] ex. 321
> fax.  [phone]
> agerson@stripped
> 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 <agersonl@stripped
> <mailto:agersonl@stripped>>
> >
> >     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. [phone] <tel:[phone]> ex. 321
> >     fax. [phone] <tel:[phone]>
> >     agerson@stripped <mailto:agerson@stripped>
> >     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@ style="color:#666">stripped
> >     <http://lists.mysql.com/mysql?unsub=1
> >
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>
>