List: General Discussion « Previous MessageNext Message » From: Shawn Green Date: May 1 2010 3:49am Subject: Re: Variables in stored procedures View as plain text
```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.
###

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
```