Re: Variables in stored procedure

[Adam Gerson]

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
>
>