[firebird-support] EXECUTE STATEMENT problem understanding

[Alan J Davies]

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