开发者

Correct Syntax for Mysql 5 stored procedure?

Is this the correct syntax for a mysql 5.x stored procedure?

DELIMITER $$

CREATE PROCEDURE GetNearbyPhotogsByMapCenter(
  lat1 decimal (7,3),
  long1 decimal (7,3),
  range  numeric (15)
)
BEGIN
DECLARE  rangeFactor  decimal (7,6);
 SET  rangeFactor = 0.014457;
 select * from (
  SELECT B.cb_plug_lat, B.cb_plug_lng, B.c开发者_开发百科b_photostudio , B.city, B.State,
  B.country, B.website, B.cb_basesserved, B.phone,
  B.cb_isrc,B.cb_isavailableforsessions
   FROM  jos_comprofiler AS  B, jos_users as JU
  WHERE
  B.cb_plug_lat  BETWEEN  lat1-(range*rangeFactor)  AND
   lat1+(range*rangeFactor)
  AND  B.cb_plug_lng  BETWEEN  long1-(range*rangeFactor)  AND
   long1+(range*rangeFactor)
  AND  GetDistance(lat1,long1,B.cb_plug_lat,B.cb_plug_lng)  <= range
  AND B.approved = 1
  AND B.confirmed = 1
  AND B.user_id = JU.id
  ORDER BY B.cb_isrc desc) as D
  WHERE D.cb_isavailableforsessions = 'Yes' or  D.cb_isavailableforsessions is null;
END
$$

I am trying to load this stored procedure into my MySQL database using the SQL tab on my phpMyAdmin. I did set the delimeter in the bottom of that tab to $$ as per this post: How do I write an SP in phpMyAdmin (MySQL)?

So I am just curious why I keep getting this error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'lat1 decimal (7,3), long1 decimal (7,3), range numeric (15) BEGIN ' at line 2


Don't put procedure identifier into quotes. Also range is a MySQL reserved word (since 5.1), so you either need to put it in backticks (`) everytime you use it, or change it to a non-reserved word.

Below is the version that did register a procedure on my MySQL 5.2 server

CREATE PROCEDURE GetNearbyPhotogsByMapCenter(
  lat1 decimal (7,3),
  long1 decimal (7,3),
  `range`  numeric (15)
)
BEGIN
DECLARE  rangeFactor  decimal (7,6);
 SET  rangeFactor = 0.014457;
 select * from (
  SELECT B.cb_plug_lat, B.cb_plug_lng, B.cb_photostudio , B.city, B.State,
  B.country, B.website, B.cb_basesserved, B.phone,
  B.cb_isrc,B.cb_isavailableforsessions
   FROM  jos_comprofiler AS  B, jos_users as JU
  WHERE
  B.cb_plug_lat  BETWEEN  lat1-(`range`*rangeFactor)  AND
   lat1+(`range`*rangeFactor)
  AND  B.cb_plug_lng  BETWEEN  long1-(`range`*rangeFactor)  AND
   long1+(`range`*rangeFactor)
  AND  GetDistance(lat1,long1,B.cb_plug_lat,B.cb_plug_lng)  <= `range`
  AND B.approved = 1
  AND B.confirmed = 1
  AND B.user_id = JU.id
  ORDER BY B.cb_isrc desc) as D
  WHERE D.cb_isavailableforsessions = 'Yes' or  D.cb_isavailableforsessions is null;
END
$$
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜