Ashley M. Kirchner wrote:
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?
Two options
1) use the placeholder, a question mark, to represent the location to
which you want a variable to appear then pass those variables in as part
of your execute statement. The name of the table cannot be resolved as a
variable at execution time. To quote
http://dev.mysql.com/doc/refman/5.1/en/prepare.html
###
Parameter markers can be used only where data values should appear, not
for SQL keywords, identifiers, and so forth.
###
see also:
http://dev.mysql.com/doc/refman/5.1/en/execute.html
2) resolve your variables as you build your SQL string so that they no
longer refer to a variable but become literal values within the query
you are constructing.
--
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
