hi,

i've created a store procedure.
the idea is to raise an error code/message to the user if the procedure
failed and to raise a message if the procedure succeed.
i've read the language reference manual, and it said that if used, WHEN must
be the last statement in the begin..end block.

i guess i have to assign one variable. but how do this variable know that
this procedure works well or not.
fyi, i need to rollback everything if there was an error, if not, then
commit.


here it is:

set term#;
create procedure sp_move_transaction
as
declare variable v_t_seq_no integer;
declare variable v_t_no char(20);
declare variable v_t_date timestamp;
declare variable v_t_pos_code char(10);
declare variable v_t_shift integer;
declare variable v_t_cashier_id char(10);
declare variable v_t_cashier_name char(10);
declare variable v_t_product_name char(50);
declare variable v_t_prod_code integer;
declare variable v_t_qty float;
declare variable v_t_unit_price float;
declare variable v_t_amt float;
declare variable v_t_rounded_factor char(10);
declare variable v_t_tender_type integer;
declare variable v_flag integer;
declare variable v_usercreate char(10);
declare variable v_datecreate timestamp;
declare variable x varchar;
begin
select
t_seq_no,t_no,t_date,t_pos_code,t_shift,t_cashier_id,t_cashier_name,t_produc
t_name,t_prod_code,t_qty,t_unit_price,t_amt,t_rounded_factor,t_tender_type,f
lag,usercreate,datecreate
from t_transaction
where t_date = current_date
into
:v_t_seq_no,:v_t_no,:v_t_date,:v_t_pos_code,:v_t_shift,:v_t_cashier_id,:v_t_
cashier_name,:v_t_product_name,:v_t_prod_code,:v_t_qty,:v_t_unit_price,:v_t_
amt,:v_t_rounded_factor,:v_t_tender_type,:v_flag,:v_usercreate,:v_datecreate
;

insert into th_transaction
(t_seq_no,t_no,t_date,t_pos_code,t_shift,t_cashier_id,t_cashier_name,t_produ
ct_name,t_prod_code,t_qty,t_unit_price,t_amt,t_rounded_factor,t_tender_type,
flag,usercreate,datecreate)

values(:v_t_seq_no,:v_t_no,:v_t_date,:v_t_pos_code,:v_t_shift,:v_t_cashier_i
d,:v_t_cashier_name,:v_t_product_name,:v_t_prod_code,:v_t_qty,:v_t_unit_pric
e,:v_t_amt,:v_t_rounded_factor,:v_t_tender_type,:v_flag,:v_usercreate,:v_dat
ecreate);

delete from t_transaction
where t_date = current_date;

suspend;

when any do x='false'; <-------????

if (x='false') then
begin
exception failed;
rollback;
end
else
commit;
end#
set term;#

can anyone help?
thx!

Regina Phandu