Re: stored procedures
Rob:
When you use default variable definition, you have to include global prefixing
variable name. And also I see (;) after FOREACH select.
Correct one should be
create procedure test()
-- Define user variablesdefine global x1 integer default 1;
define x2 char(8);
-- Set variables
let x1 = 1;
FOREACH ordnum FOR
select can_ordnum, can_status into x2, x1 from caninfo
where can_status = 0
update caninfo set can_status = x1;END FOREACH
end procedure
Rgdds
"rob1.smith" wrote:
> attached is the stored procedure and the table layout of the table that I am
> trying to update.
> I am getting a 201 : syntax error on the define x1 integer line.
> Stored procedures are very new to me can any body help,
> email: rob.smith@hhuncare.co.uk
> or rob.smith@tinyonline.co.uk
>
> create procedure rental_1()
> -- Define user variables> define x1 integer default 1;
> define x2 char(8);
> -- Set variables
> let x1 = 1;
> FOREACH ordnum FOR
> select can_ordnum, can_status into x2, x1 from caninfo
> where can_status = 0;
> update caninfo set can_status = x1;> END FOREACH
> end procedure
>
> { TABLE caninfo row size = 372 number of columns = 20 index size = 72 }
> create table caninfo
> (
> can_ordnum char(8) not null constraint n536_7486,
> can_geog char(5) not null constraint n536_7487,
> can_account char(10) not null constraint n728_17917,
> can_name char(40) not null constraint n536_7489,
> can_addr1 char(30) not null constraint n536_7490,
> can_addr2 char(30) not null constraint n536_7491,
> can_addr3 char(30) not null constraint n536_7492,
> can_addr4 char(30) not null constraint n536_7493,
> can_addr5 char(30) not null constraint n536_7494,
> can_postcode char(10) not null constraint n724_17852,
> can_uzward char(20) not null constraint n536_7496,
> can_contact char(20) not null constraint n536_7497,
> can_item char(5) not null constraint n536_7498,
> can_sysdesc char(33) not null constraint n536_7499,
> can_remarks char(40) not null constraint n536_7500,
> can_cancnum char(9) not null constraint n536_7501,
> can_cancdate date not null constraint n536_7502,
> can_status integer not null constraint n538_7504,
> can_lud date not null constraint n539_7505,> can_lui char(10) not null constraint n539_7506
> );
> create index can_ordnum on caninfo (can_ordnum);
> create index can_account on caninfo (can_account);
> create index can_geog on caninfo (can_geog);
> create index can_cancnum on caninfo (can_cancnum);