MySQL Stored procedure variables from SELECT statements

Question

I'm trying to create a stored procedure. Here's what I have so far (not working):

DELIMITER |
CREATE PROCEDURE getNearestCities(IN cityID INT)
    BEGIN
        DECLARE cityLat FLOAT;
        DECLARE cityLng FLOAT;
        SET cityLat = SELECT cities.lat FROM cities WHERE cities.id = cityID;
        SET cityLng = SELECT cities.lng FROM cities WHERE cities.id = cityID;
        SELECT *, HAVERSINE(cityLat,cityLng, cities.lat, cities.lng) AS dist FROM cities ORDER BY dist LIMIT 10;
    END |

HAVERSINE is a function I created which works fine. As you can see I'm trying to take the id of a city from the cities table and then set cityLat and cityLng to some other values of that record. I'm obviously doing this wrong here by using SELECTs.

Is this even possible. It seems it should be. Any help whatsoever will be greatly appreciated.

Answer 1

Corrected a few things and added an alternative select - delete as appropriate.

DELIMITER |

CREATE PROCEDURE getNearestCities
(
IN p_cityID INT -- should this be int unsigned ?
)
BEGIN

DECLARE cityLat FLOAT; -- should these be decimals ?
DECLARE cityLng FLOAT;

    -- method 1
    SELECT lat,lng into cityLat, cityLng FROM cities WHERE cities.cityID = p_cityID;

    SELECT 
     b.*, 
     HAVERSINE(cityLat,cityLng, b.lat, b.lng) AS dist 
    FROM 
     cities b 
    ORDER BY 
     dist 
    LIMIT 10;

    -- method 2
    SELECT   
      b.*, 
      HAVERSINE(a.lat, a.lng, b.lat, b.lng) AS dist
    FROM     
      cities AS a
    JOIN cities AS b on a.cityID = p_cityID
    ORDER BY 
      dist
    LIMIT 10;

END |

delimiter ;

Answer 2

You simply need to enclose your SELECT statements in parentheses to indicate that they are subqueries:

SET cityLat = (SELECT cities.lat FROM cities WHERE cities.id = cityID);

Alternatively, you can use MySQL's SELECT ... INTO syntax. One advantage of this approach is that both cityLat and cityLng can be assigned from a single table-access:

SELECT lat, lng INTO cityLat, cityLng FROM cities WHERE id = cityID;

However, the entire procedure can be replaced with a single self-joined SELECT statement:

SELECT   b.*, HAVERSINE(a.lat, a.lng, b.lat, b.lng) AS dist
FROM     cities AS a, cities AS b
WHERE    a.id = cityID
ORDER BY dist
LIMIT    10;