mysql 存储过程 select 变量,MySQL SELECT语句中的存储过程变量

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.

解决方案

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 ;