List:General Discussion« Previous MessageNext Message »
From:Ashley M. Kirchner Date:May 1 2010 1:14am
Subject:Variables in stored procedures
View as plain text  
     I have the following procedure:

CREATE PROCEDURE `geodistance`(IN userid int, IN maxdist int)
   declare userlng double;
   declare userlat double;
   declare lng1 float;
   declare lng2 float;
   declare lat1 float;
   declare lat2 float;

   select lng, lat into userlng, userlat from coords_tbl where id=userid 
limit 1;

   set lng1 = userlng-maxdist/abs(cos(radians(userlat))*69);
   set lng2 = userlng+maxdist/abs(cos(radians(userlat))*69);
   set lat1 = userlat-(maxdist/69);
   set lat2 = userlat+(maxdist/69);

   select id,lat,lng,3959*2*asin(sqrt(power(sin((userlat - abs(lat)) * 
pi()/180/2),2) + cos(userlat*pi()/180) * cos(abs(lat) * pi()/180) * 
power(sin((userlng - lng) * pi()/180/2),2))) as distance from coords_tbl 
where lng between lng1 and lng2 and lat between lat1 and lat2 having 
distance < maxdist order by distance;

     This works just dandy ... with one small problem.  I need to be 
able to pass a variable to the procedure that represents the table it 
should be querying.  At the moment it's querying the hard coded table 
'coords_tbl' ...

     So the create statement should change to:

      'CREATE PROCEDURE `geodistance` (IN userid int, IN maxdist int, IN 
tblname char)'

     But then, how do I pass that to the select queries after wards?

     I tried setting a @tmp_query which is a CONCAT('select ... from ', 
tblname, ' ...'); and using prepare and execute to run it, but it always 
comes back telling me 'userlng' is undefined (presumably because that's 
the first one in the select query).  So I'm missing something, somewhere ...

     Suggestions anyone?

Variables in stored proceduresAshley M. Kirchner1 May
  • Re: Variables in stored proceduresShawn Green1 May
    • RE: [MySQL] Re: Variables in stored proceduresAshley M. Kirchner1 May
RE: [MySQL] Re: Variables in stored proceduresAshley M. Kirchner1 May