From: Shawn Green
Date: May 1 2010 3:49am
Subject: Re: Variables in stored procedures
List-Archive: http://lists.mysql.com/mysql/221478
Message-Id: <4BDBA4B8.7020504@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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