3

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;
Get Off My Lawn
  • 34,175
  • 38
  • 176
  • 338

1 Answers1

4

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

peterm
  • 91,357
  • 15
  • 148
  • 157
  • I can't do that with prepared statements can I? – Get Off My Lawn Aug 26 '13 at 01:02
  • 1
    @RyanNaddy If you mean with `PREPARE ... FROM ...` and `EXECUTE ... USING` then yes you can but you need to use user/session variables (`e.g. @lat`) instead of local variables that goes without `@`. – peterm Aug 26 '13 at 01:05
  • Okay I did this (as prepared stmt): `SELECT latitude, longitude INTO @lat, @lon .... ` then this (as normal): `select @lat, @lon into lat, lon;` – Get Off My Lawn Aug 26 '13 at 01:07