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
$$
精彩评论