Variables in stored procedures

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?


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Variables in stored procedures

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Re: Variables in stored procedures

------=_NextPart_000_0082_01CAE8BB.D0FA3F70
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

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

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

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

> To: Ashley M. Kirchner

> Cc: mysql@lists.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 .




------=_NextPart_000_0082_01CAE8BB.D0FA3F70--

RE: Re: Variables in stored procedures

------=_NextPart_000_001C_01CAE921.73C89C00
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

Huh? That doesn't make a whole lot of sense. If that is the case, can you
explain why? Is the stored procedure mechanism that primitive?



From: Ted Yu [mailto:yuzhihong@gmail.com]
Sent: Saturday, May 01, 2010 7:11 AM
To: Ashley M. Kirchner
Subject: Re: [MySQL] Re: Variables in stored procedures



I am afraid you need to construct the whole stored procedure using CONCAT.




------=_NextPart_000_001C_01CAE921.73C89C00--