MySQL Forums
Forum List  »  Stored Procedures

Re: Variable as table-name
Posted by: Roland Bouman
Date: December 09, 2005 01:41PM

You can only do that using the prepred statement syntax.
(see: http://dev.mysql.com/doc/refman/5.0/en/sqlps.html and http://mysql.gilfster.com/page.php?parent_id=1.3&page_id=1.3.6)

Unfortunately, you can't prepare SELECT...INTO statements like you do.
But you can work around it:

set @vgid_num := vgid_num; -- need user variable for prepared statement param

set @stmt1 := concat(
'SELECT @curr_table_n:=name INTO '
, ' FROM '
, tbl_n
, ' WHERE _date'
);
prepare stmt1 from @stmt1;

REPEAT

set @curr_table_n:=NULL;
execute stmt1;

if NOT @curr_table_n IS NULL then
set @stmt2 := concat(
'SELECT @a:=sum(amount)'
, ', @b:=sum(cin)'
, ', @c:=sum(duration)'
, ' FROM '
, @curr_table_n
, ' WHERE vg_id = ?'
);
prepare stmt2 from @stmt2;
execute stmt2 using @vgid_num; --set the parameter
deallocate stmt2;
end if;
..
..
UNTIL...

deallocate stmt1;
..


so, instead of select into, we just use user variables to mimic the effect.

BTW, your repeat loop is potentially buggy. The loop always runs once, even if the finish_d parameter is earlier than the initial value of curr_table_d ('2005-01-01')

Options: ReplyQuote


Subject
Views
Written By
Posted
37874
December 09, 2005 09:27AM
Re: Variable as table-name
17487
December 09, 2005 01:41PM
9665
December 10, 2005 07:57AM
10225
December 11, 2005 08:29AM
8588
December 12, 2005 07:57AM
8272
December 12, 2005 08:48AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.