Ashley M. Kirchner wrote:
> 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?
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
Parameter markers can be used only where data values should appear, not
for SQL keywords, identifiers, and so forth.
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.
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN