MySQL Stored procedure variables from SELECT statements

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] MySQL Stored procedure variables from SELECT statements

Examples related to mysql

Examples related to stored-procedures