CODEWITHSUNDEEP
mysqlfunction
Ronon
Ronon

Reputation: 842

MySQL function throws syntax error

I have a query which calculates the distance with given lat and lng and POINTs.

Now i want to make it a function:

DELIMITER $$

CREATE FUNCTION CalculateDistance(geobreite double, geolaenge double, umkreis int) RETURNS INT READS SQL DATA

BEGIN

SET @ibk_laenge = geobreite;
SET @ibk_breite = geolaenge;
SET @umkreis = umkreis;
SET @breite_nord = @ibk_breite + (@umkreis / 111);
SET @breite_sued = @ibk_breite - (@umkreis / 111);
SET @laenge_west = @ibk_laenge - (@umkreis / ABS(COS(RADIANS(@ibk_breite))) * 111);
SET @laenge_ost = @ibk_laenge + (@umkreis / ABS(COS(RADIANS(@ibk_breite))) * 111);

SET @mp = CONCAT('MULTIPOINT(', @breite_sued , ' ', @laenge_west, ', ', @breite_nord, ' ', @laenge_ost, ')');

SET @quadrat = ENVELOPE(GEOMFROMTEXT(@mp));

RETURN 
    SELECT (
        FLOOR(
            SQRT(
                POW((@ibk_breite - sub.breite) * 111, 2) + 
                POW((@ibk_laenge - sub.laenge) * 111 * ABS(COS(RADIANS(@ibk_breite))),2)
            )
        )

    ) AS distanz

    FROM
        (
            SELECT Y(location) AS laenge, X(location) AS breite FROM meetings WHERE MBRCONTAINS(@quadrat, location)
        ) 
    AS sub;

END; $$ 

DELIMITER ;

But it throws an 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
'SELECT (
FLOOR(
SQRT(
POW((@i' at line 18

If i execute the query with phpmyadmin it works without any problems.

Upvotes: 0

Views: 77

Answers (1)

Devart
Devart

Reputation: 121922

To fix syntax error try to wrap SELECT query to brackets, e.g. -

RETURN (SELECT column1 FROM table WHERE id = 1);

Next problem: function must return one scalar value, if SELECT query returns some records, you will get an error.

Also, you can use SELECT...INTO query to write value to variable, and after just return it:

SELECT FLOOR(SQRT(...)) INTO @ret FROM ...;
RETURN @ret;

Upvotes: 1

Related Questions