CODEWITHSUNDEEP
mysqlstored-proceduresout-parameters
Get Off My Lawn
Get Off My Lawn

Reputation: 36311

Mysql Stored Procedure Select Column into OUT parameter

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;

Upvotes: 3

Views: 6101

Answers (1)

peterm
peterm

Reputation: 92785

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

Upvotes: 4

Related Questions