MySQL Stored Procedure Variables from SELECT Statements

Question

I am trying to create a stored procedure. Here is what I still (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 that I created that works great. As you can see, I am trying to take the city identifier from the city table and then set cityLat and cityLng for some other values ​​of this record. I obviously am doing it wrong using SELECT.

Is it possible. It seems like it should be. Any help would be greatly appreciated.

Answer 1

Several things have been fixed and an alternative option has been added - delete as necessary.

 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 just need to enclose the SELECT in parentheses to indicate that they are subqueries:

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

Alternatively, you can use the MySQL SELECT ... INTO syntax. One of the advantages 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 one independent SELECT :

 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;