From: Ashley M. Kirchner (ashleypcraft.com)
Date: Sat May 01 2010 - 00:21:06 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

> -----Original Message-----

> From: Shawn Green [mailto:shawn.l.greenoracle.com]

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

> To: Ashley M. Kirchner

> Cc: mysqllists.mysql.com

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]