On Fri, 26 Jul 2013 09:50:43 +0100, Alan J Davies
<Alan.Davies@...> wrote:

> 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 statement to be passed to EXECUTE STATEMENT needs to be the entire
statement (optionally with parametrized *values*). You are currently trying
to pass a statement 'update q_tool', which is not valid (it is missing a
'SET'-clause, and you are trying to add a `WHERE`-clause to EXECUTE
STATEMENT itself, which is not possible.

What you need to do is roughly:

s = 'update q_tool set tgp_no = :tgp_no, del_date = :del_date where pjs_no
= :pjs_no'
EXECUTE STATEMENT (s) (tgp_no := tgp_no, del_date := del_date, pjs_no :=
pjs_no);

Mark