Error in the first line creating mysql stored procedure

Question

I am trying to create a stored procedure in MySQL Community Server 5.5.32 version but I procedure returns an error in the first line but I can not find it. Can anyone help me?

CREATE PROCEDURE nearestPanel (IN mylat DOUBLE, IN mylon DOUBLE) 
BEGIN
    DECLARE dist DOUBLE DEFAULT 62.14;
    DECLARE lon1 DOUBLE DEFAULT 0;
    DECLARE lon2 DOUBLE DEFAULT 0;
    DECLARE lat1 DOUBLE DEFAULT 0;
    DECLARE lat2 DOUBLE DEFAULT 0;
    DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;

    SET lon1 = mylon-dist/abs(cos(radians(mylat))*69);
    SET lon2 = mylon+dist/abs(cos(radians(mylat))*69);
    SET lat1 = mylat-(dist/69);
    SET lat2 = mylat+(dist/69);

SELECT 
    panels.name, 
    panels.description, 
    cells.*, 
    3956 * 2 * ASIN(SQRT(POWER(SIN((mylat - panels.latitude) * pi()/180 / 2), 2) +COS(mylat * pi()/180) * COS(panels.latitude * pi()/180) * POWER(SIN((mylon -panels.longitude) * pi()/180 / 2), 2))) AS distance
FROM 
    cells 
INNER JOIN 
    panels
ON 
    cells.panel_id = panels.id
WHERE 
    panels.longitude BETWEEN lon1 AND lon2 
AND 
    panels.latitude BETWEEN lat1 AND lat2 
AND 
    today BETWEEN panels.date_start AND panels.date_end
HAVING 
    distance < dist 
ORDER BY 
    distance LIMIT 1;
END

Seems to work with a // as delimiter. Try just first typing delimiter //, and add your procedure with a // after the last line. Then you can give delimiter ; to go back to a semicolon delimiter. — Joachim Isaksson
– Joachim Isaksson, Commented Dec 14, 2013 at 15:52
Since it's hard to expand in comments, I added more detail in an answer below. — Joachim Isaksson
– Joachim Isaksson, Commented Dec 23, 2013 at 19:41

Joachim Isaksson · Accepted Answer · 2013-12-23 19:41:20Z

3

Since the procedure contains semicolons, you'll need to temporarily change the delimiter from ;, for example to //;

DELIMITER //
CREATE PROCEDURE nearestPanel (IN mylat DOUBLE, IN mylon DOUBLE) 
BEGIN
    DECLARE dist DOUBLE DEFAULT 62.14;
    DECLARE lon1 DOUBLE DEFAULT 0;
...
    HAVING 
    distance < dist 
ORDER BY 
    distance LIMIT 1;
END//
DELIMITER ;

An SQLfiddle to test with, note the delimiter set to // in the dropdown below the code.

answered Dec 23, 2013 at 19:41

Joachim Isaksson

182k28 gold badges298 silver badges308 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jcancig Over a year ago

Ah! Perfect! Sorry for not understanding the previous explanation. Works fine, thanks

Collectives™ on Stack Overflow

Error in the first line creating mysql stored procedure

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related