RE: stored procedures
> 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
>
Try
CREATE PROCEDURE rental_1()
UPDATE caninfo
SET can_status = 1
WHERE can_status = 0;
END PROCEDURE;
However I'm not totally sure that this is what your trying to achieve.
It could update a lot of records without a more restrictive Where clause?
You may be trying to do
CREATE PROCEDURE rental_1( p_can_ordnum CHAR(8))
UPDATE caninfo
SET can_status = 1
WHERE can_status = 0
AND can_ordnum = p_can_ordnum ;
END PROCEDURE;
Jo
>
> 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);>
>
>
___________________________________________________________________________
This email is confidential and intended solely for the use of the
individual to whom it is addressed. Any views or opinions presented are
solely those of the author and do not necessarily represent those of
Sema Group.
If you are not the intended recipient, be advised that you have received this
email in error and that any use, dissemination, forwarding, printing, or
copying of this email is strictly prohibited.
If you have received this email in error please notify the Sema Group
Helpdesk by telephone on +44 (0) 121 627 5600.
___________________________________________________________________________