I have the following procedure:
----------
CREATE PROCEDURE `geodistance`(IN userid int, IN maxdist int)
begin
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?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
