Simple informix select stored procedure

Question

I am trying to write a simple stored procedure that does a select statement but it keeps giving me a syntax error without any other help telling me what the error is

create procedure _this_is_it(this CHAR(3), that CHAR(4), other CHAR(3))
foreach select * from table 
where column1 = this and
column2 = that and
column3 = other
end foreach
end procedure;

Any reason why I should get a syntax error?

Solution 1

Remove the foreach and the _ as first letter, also add a ; at the end of the select.

create procedure this_is_it(this CHAR(3), that CHAR(4), other CHAR(3))

select * from table 
where column1 = this and
column2 = that and
column3 = other;

end procedure;

Solution 2

If you want to perform some processing inside a foreach loop you have to SELECT INTO variables. So your code would become:

drop procedure if exists _this_is_it ;
drop table if exists this_table ;
create temp table this_table(column1 CHAR(3), column2 CHAR(4), column3 CHAR(3))
with no log ;
insert into this_table values("A","B","C") ;

create procedure _this_is_it(this CHAR(3), that CHAR(4), other CHAR(3))
returns CHAR(10) AS something

define f_this CHAR(3) ;
define f_that CHAR(4) ;
define f_other CHAR(3) ;

foreach
select * into f_this, f_that, f_other
from this_table
where column1 = this and
column2 = that and
column3 = other

return f_this||f_that||f_other WITH RESUME ;

end foreach
end procedure;

grant execute on _this_is_it to public;
execute procedure _this_is_it("A","B","C") ;

drop procedure if exists _this_is_it ;
drop table if exists this_table ;

Syntax for the FOREACH statement is available from IBM at their Infocenter.