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.
This question is related to
mysql
stored-procedures
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;
I am facing a strange behavior.
SELECT INTO and SET Both works for some variables and not for others. Event syntaxes are the same
SET @Invoice_UserId := (SELECT UserId FROM invoice WHERE InvoiceId = @Invoice_Id LIMIT 1); -- Working
SET @myamount := (SELECT amount FROM invoice WHERE InvoiceId = @Invoice_Id LIMIT 1); - Not working
SELECT Amount INTO @myamount FROM invoice WHERE InvoiceId = 29 LIMIT 1; - Not working
If I run these queries directly then works, but not working in stored procedure.
Source: Stackoverflow.com