Mysql Stored Procedure Select Column into OUT parameter

5.9k views Asked by At

I have a stored procedure similar to this one, and when I try to save it, I get this error:

Undeclared variable: my_column

CREATE PROCEDURE p (OUT column_param VARCHAR(25))
BEGIN
    SELECT my_column INTO coumn_param limit 1;
END;

Why can I not select a column to return?

Here my actual query within the procedure:

select latitude into lat, longitude into lon
from cities cc
inner join countries c on cc.country_id = c.country_id
inner join regions r on cc.region_id = r.region_id and c.country_id = r.country_id
left join locations l on cc.city_id = l.city_id
where 
city = coalesce(cty, city) and
country = coalesce(ctry, country) and
region = coalesce(reg, region)
limit 1;
1

There are 1 answers

3
peterm On BEST ANSWER

Your syntax of INTO clause is incorrect

Assuming that your query itself is correct and functional and you have lat and lon variables declared change this part

select latitude into lat, longitude into lon

to

SELECT latitude, longitude INTO  lat, lon

Here is SQLFiddle demo