MySQL Stored procedure variables from SELECT statements
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 ;
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;