CODEWITHSUNDEEP
mysqlstored-procedures
jcancig
jcancig

Reputation: 1

Error in the first line creating mysql stored procedure

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

Upvotes: 0

Views: 262

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180887

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.

Upvotes: 3

Related Questions