Hi all, I have a number of sps similar to this. Basically, I update,
insert or delete according to the parameter "actiontype" Then according
to the parameter "tgp" I use the q_tool table, the q_gauge table or the
q_ppap table. Everything else is identical and basically I want one set
of actions with the table name as a parameter (if I explain that
correctly) along these lines just for the first option:

create or alter procedure q_upd_tool_gauge_ppap_test (
pjs_no integer,
tgp_no integer,
del_date date,
tgp char(1),
actiontype integer)
as
declare variable s varchar(100);
begin
s='update q_tool';
if (TGP='T') then
begin
if (:actiontype=1) then /* Update */
begin
execute statement (:s) /* q_tool */
( tgp_no:=:tgp_no,del_date:=:del_date);
where pjs_no=:pjs_no);
end

The actual SP is below and I have removed the majority of the fields for
ease of reading.

create or alter procedure q_upd_tool_gauge_ppap (
pjs_no integer,
tgp_no integer,
del_date date,
tgp char(1),
actiontype integer)
as
begin
if (TGP='T') then
begin
if (:actiontype=1) then /* Update */
begin
update q_tool
set tgp_no=:tgp_no,del_date=:del_date
where pjs_no=:pjs_no;
end
else if (ActionType=2) then /* Insert */
begin
insert into q_tool (pjs_no,tgp_no,del_date)
values (:pjs_no,:tgp_no,:del_date);
end
else if (ActionType=3) then /* Delete */
begin
delete from q_tool
where pjs_no=:pjs_no;
end
end
else if (TGP='G') then
begin
if (:actiontype=1) then /* Update */
begin
update q_gauge
set tgp_no=:tgp_no,del_date=:del_date
where pjs_no=:pjs_no;
end
else if (ActionType=2) then /* Insert */
begin
insert into q_gauge (pjs_no,tgp_no,del_date)
values (:pjs_no,:tgp_no,:del_date);
end
else if (ActionType=3) then /* Delete */
begin
delete from q_gauge
where pjs_no=:pjs_no;
end
end
else if (TGP='P') then
begin
if (:actiontype=1) then /* Update */
begin
update q_ppap
set tgp_no=:tgp_no,del_date=:del_date
where pjs_no=:pjs_no;
end
else if (ActionType=2) then /* Insert */
begin
insert into q_ppap (pjs_no,tgp_no,del_date)
values (:pjs_no,:tgp_no,:del_date);
end
else if (ActionType=3) then /* Delete */
begin
delete from q_ppap
where pjs_no=:pjs_no;
end
end
when SQLCode -803 Do
Exception insertException;/* Already On File */
when SQLCode -530 Do
Exception deleteException;/* Deliveries On Order File */
end

Thanks in advance for any guidance.
Alan

Alan J Davies
Aldis