Mysql CREATE FUNCTION syntax error

Question

CREATE DEFINER = CURRENT_USER FUNCTION GET_DISTANCE (LatBegin float,LngBegin float,LatEnd float,LngEnd float)
    RETURNS float
    BEGIN
        DECLARE Distance FLOAT
    DECLARE EARTH_RADIUS FLOAT
    SET EARTH_RADIUS =  6378137.00

    DECLARE dlat FLOAT
            DECLARE dlng FLOAT
    SET LatBegin =  LatBegin * PI()/ 180.0
    SET LngBegin = LngBegin * PI() / 180.0
        SET LatEnd = LatEnd * PI() / 180.0
    SET LngEnd = LngEnd * PI() / 180.0

    SET dlat =  LatBegin - LatEnd
    SET dlng = LngBegin - LngEnd

    SET Distance = (1-cos(dlat))/2.0+cos(LatBegin)*cos(LatEnd)*((1-cos(dlng))/2.0)
    SET Distance = asin(sqrt(Distance)) * EARTH_RADIUS *2.0
    SET Distance = Round(Distance * 10000,2) / 10000
    RETURN Distance
    END

[Err] 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 'DECLARE v_dlat FLOAT; DECLARE dlng FLOAT; SET LatBegin = LatBegin * PI(' at line 8

Álvaro González · Accepted Answer · 2012-04-10 09:07:36Z

2

You've written the whole function as one big statement. You need to use delimiters. Here's the example from the MySQL manual:

mysql> delimiter //

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)

answered Apr 10, 2012 at 9:07

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

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

Comments

Manse · Accepted Answer · 2012-04-10 09:06:18Z

0

You must end each line/command with a ; so

BEGIN
    DECLARE Distance FLOAT;
    DECLARE EARTH_RADIUS FLOAT;
    SET EARTH_RADIUS =  6378137.00;

etc .....

answered Apr 10, 2012 at 9:06

Manse

38.1k11 gold badges88 silver badges112 bronze badges

Comments

Devart · Accepted Answer · 2012-04-10 09:25:21Z

Add ';' as ManseUK suggested.

All declarations must be at the begining of the BEGIN...END clause -

CREATE DEFINER = CURRENT_USER FUNCTION GET_DISTANCE(LatBegin FLOAT,
                                                    LngBegin FLOAT,
                                                    LatEnd   FLOAT,
                                                    LngEnd   FLOAT
                                                    )
RETURNS FLOAT
BEGIN
  DECLARE Distance     FLOAT;
  DECLARE EARTH_RADIUS FLOAT;
  DECLARE dlat         FLOAT;
  DECLARE dlng         FLOAT;

  SET EARTH_RADIUS = 6378137.00;
  SET LatBegin = LatBegin * PI() / 180.0;
  SET LngBegin = LngBegin * PI() / 180.0;
  SET LatEnd = LatEnd * PI() / 180.0;
  SET LngEnd = LngEnd * PI() / 180.0;
  SET dlat = LatBegin - LatEnd;
  SET dlng = LngBegin - LngEnd;

  SET Distance = (1 - COS(dlat)) / 2.0 + COS(LatBegin) * COS(LatEnd) * ((1 - COS(dlng)) / 2.0);
  SET Distance = ASIN(SQRT(Distance)) * EARTH_RADIUS * 2.0;
  SET Distance = ROUND(Distance * 10000, 2) / 10000;
  RETURN Distance;
END

Collectives™ on Stack Overflow

Mysql CREATE FUNCTION syntax error

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related