> -----Original Message----- > From: Shawn Green [mailto:shawn.l.gr...@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 .
