Create procedure problem

[Joe Byers]

I want to create a stored procedure that I pass a date variable and two 
database names.  I can not seem to figure the correct syntax to utilize 
these variables in the procedure.  I think I might need to build a 
sqlstring and use an exec command to execute the string.  Here is my 
procedure code:

# requires a table of paths called paths;
create procedure calclmp(in startdate date, in db1 varchar(32), in db2 
varchar(32) );
begin
drop table if exists DB1.lmp_daily, DB1.t6, DB1.tmp_month_hr;
create table DB1.lmp_daily like DB2.pjm_lmp_daily;

/*create table DB1.lmp_daily (_FREQ_ int, lmp_mean real,lmp_std 
real,class_type varchar(7))*/
insert into DB1.lmp_daily
   select if(si.hourtype=1,"OnPeak","OffPeak") as class_type,si.date, 
p.source,p.sink,
       count(si.price-so.price) as _FREQ_, sum(si.price-so.price) as 
lmp_sum,
       avg(si.price-so.price) as lmp_mean,
          
sqrt(variance(si.price-so.price)*count(si.price-so.price)/(count(si.price-so.price)-1)) 
as lmp_std
      from DB1.paths as p,DB2.lmprices_t as si, DB2.lmprices_t as so
      where p.source=so.pnodeid and p.sink=si.pnodeid
         and si.date>= STARTDATE and so.date>= STARTDATE
       and si.date=so.date and si.hour=so.hour and si.hourtype =so.hourtype
      group by si.date, p.source, p.sink, class_type;
insert into DB1.lmp_daily
   select "24H" as class_type, si.date, p.source,p.sink, 
count(si.price-so.price) as _FREQ_,
          sum(si.price-so.price) as lmp_sum,
       avg(si.price-so.price) as lmp_mean,
          
sqrt(variance(si.price-so.price)*count(si.price-so.price)/(count(si.price-so.price)-1)) 
as lmp_std
      from DB1.paths as p,DB2.lmprices_t as si, DB2.lmprices_t as so
      where p.source=so.pnodeid and p.sink=si.pnodeid
         and si.date>= STARTDATE and so.date>= STARTDATE
       and si.date=so.date and si.hour=so.hour /*and si.hourtype 
=so.hourtype */
      group by si.date, p.source, p.sink;

create table DB1.t6 (lmp real, lmp_option_long real, lmp_option_short real)
   select date, source, sink, class_type,lmp_sum as lmp,
       /*max*/if(lmp_sum>0,lmp_sum,0) as lmp_option_long,
       /*min*/if(lmp_sum<0,lmp_sum,0) as lmp_option_short
   from DB1.lmp_daily;
create table DB1.tmp_month_hr (year int, month int, _FREQ_ int, lmp_sum 
real, lmp_mean real, lmp_std real,
       Freq_minus int, freq_plus int,
       lmp_option_long_mean real, lmp_option_long_sum real, 
lmp_option_long_std real,
       lmp_option_short_mean real, lmp_option_short_sum real, 
lmp_option_short_std real,
       lmp_min real, lmp_max real, lmp_option_long_min real, 
lmp_option_long_max real,
       lmp_option_short_min real, lmp_option_short_max real)
   select year(date) as year, month(date) as month, source, sink, 
class_type, count(lmp) as _FREQ_,
       avg(lmp) as lmp_mean, avg(lmp_option_long) as 
lmp_option_long_mean, avg(lmp_option_short) as lmp_option_short_mean,
        sum(lmp) as lmp_sum, sum(lmp_option_long) as 
lmp_option_long_sum, sum(lmp_option_short) as lmp_option_short_sum,
       sqrt(variance(lmp)*count(lmp)/(count(lmp)-1)) as lmp_std,
       
sqrt(variance(lmp_option_long)*count(lmp_option_long)/(count(lmp_option_long)-1)) 
as lmp_option_long_std,
       
sqrt(variance(lmp_option_short)*count(lmp_option_short)/(count(lmp_option_short)-1)) 
as lmp_option_short_std,
       min(lmp) as lmp_min,max(lmp) as lmp_max, min(lmp_option_long) as 
lmp_Option_long_min,
       max(lmp_option_long) as lmp_option_long_max, 
min(lmp_option_short) as lmp_Option_short_min,
       max(lmp_option_short) as 
lmp_option_short_max,count(if(lmp>0,lmp,null)) as Freq_plus,
       count(if(lmp<0,lmp,null)) as minus
   from DB1.t6
   group by year, month, source, sink, class_type;
drop table if exists DB1.t6;
end;

I appreciate any suggestions.
Thank you
Joe

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]