List:General Discussion« Previous MessageNext Message »
From:Ashley M. Kirchner Date:May 1 2010 5:21am
Subject:RE: [MySQL] Re: Variables in stored procedures
View as plain text  
> -----Original Message-----

> From: Shawn Green [mailto:shawn.l.green@stripped]

> Sent: Friday, April 30, 2010 9:49 PM

> To: Ashley M. Kirchner

> Cc: mysql@stripped

> Subject: [MySQL] Re: Variables in stored procedures

> 

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

 

Thanks for the reply Shawn.  I'm trying the fill in the variable and I can
see it being replaced and all, but then it fails with the undeclared
variable error:

 

CREATE PROCEDURE `geodistance`(IN userid int, IN maxdist int, IN tblname
varchar(20))

begin

declare userlng double;

declare userlat double;

declare lng1 float;

declare lng2 float;

declare lat1 float;

declare lat2 float;

 

set @tmpquery = CONCAT('select lng, lat into userlng, userlat from ',
tblname, ' where id=userid limit 1');

prepare query from @tmpquery;

execute query;

deallocate prepare query;

 

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);

 

set @tmpquery = CONCAT('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 ', tblname, ' where lng between
lng1 and lng2 and lat between lat1 and lat2 having distance < maxdist order
by distance');

prepare query from @tmpquery;

execute query;

deallocate prepare query;

 

 

When I run that, I get:

 

mysql> call geodistance(231, 2, 'coords_tbl');  

ERROR 1327 (42000): Undeclared variable: userlng

 

mysql> select @tmpquery; 

+---------------------------------------------------------------------------
----+

| @tmpquery
|

+---------------------------------------------------------------------------
----+

| select lng, lat into userlng, userlat from coords_tbl where id=userid
limit 1 |

+---------------------------------------------------------------------------
----+

 

The query is correct, so why does it complain that userlng isn't declared
when I declared it at the very top?  I also tried the place holder route and
get the same error .

 


Thread
Variables in stored proceduresAshley M. Kirchner1 May
  • Re: Variables in stored proceduresShawn Green1 May
    • RE: [MySQL] Re: Variables in stored proceduresAshley M. Kirchner1 May
RE: [MySQL] Re: Variables in stored proceduresAshley M. Kirchner1 May