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
