CODEWITHSUNDEEP
mysqlstored-procedures
Matt Harrison
Matt Harrison

Reputation: 13567

MySQL Stored procedure variables from SELECT statements

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.

Upvotes: 21

Views: 138395

Answers (3)

Kamlesh Kumar
Kamlesh Kumar

Reputation: 87

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.

Upvotes: 1

eggyal
eggyal

Reputation: 125855

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;

Upvotes: 20

Jon Black
Jon Black

Reputation: 16559

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 ;

Upvotes: 27

Related Questions