On Fri, 26 Jul 2013 09:50:43 +0100, Alan J Davies <alan.dav...@aldis-systems.co.uk> 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
