Hi again all, thanks to Mark and Matkus I have successfully converted a
number of SPs using this technique, but cannot get this particular
version to work. I have tried every combination I can think of and
looked and re-looked at the example code and my own code.
The error I get every time (regardless of where the parameters are
placed) is:
The insert and delete options work perfectly

Overflow occurred during data type conversion.
conversion error from string "CT ".
At procedure 'Q_UPD_TOOL_GAUGE_PPAP_PARTNO' line: 31, col: 9.

If I step through the SP all the parameters display the correct data.

create or alter procedure q_upd_tool_gauge_ppap_partno (
acno account_no,
partno part_no,
pjs_no decimals_0,
new_pjs_no decimals_0,
tgp char(1),
actiontype integer)
as
declare variable table_to_use varchar(30);
declare variable stmnt varchar(500);
begin
if (TGP='T') then
table_to_use='q_tool_partno';
else if (TGP='G') then
table_to_use='q_gauge_partno';
else if (TGP='P') then
table_to_use='q_ppap_partno';
if (actiontype=1) then /* Update */
begin

/* the actual operation
update q_gauge_partno
set pjs_no=:new_pjs_no
where acno=:acno
and partno=:partno
and pjs_no=:pjs_no; end of actual operation */

stmnt=
' update '|| table_to_use ||
' set pjs_no=:new_pjs_no
where acno=:acno
and partno=:partno
and pjs_no=:pjs_no';
execute statement (stmnt)

(acno:=acno,partno:=partno,pjs_no:=pjs_no,new_pjs_no:=new_pjs_no);
end
else if (ActionType=2) then /* Insert */
begin
stmnt=
' insert into '|| table_to_use ||
' (acno,partno,pjs_no)
values
(:acno,:partno,:pjs_no)';
execute statement (stmnt)
(pjs_no:=pjs_no,acno:=acno,partno:=partno);
end
else if (ActionType=3) then /* Delete */
begin
stmnt=
' delete from '|| table_to_use ||
' where acno=:acno
and partno=:partno
and pjs_no=:pjs_no';
execute statement (stmnt)
(acno:=acno,partno:=partno,pjs_no:=pjs_no);
end
when SQLCode -803 Do
Exception insertException;/* Already On File */
when SQLCode -530 Do
Exception deleteException;/* Deliveries On Order File */
end

Alan J Davies
Aldis
+44 (0) 1926 842069
+44 (0) 7885 372793

On 26/07/2013 13:47, Mark Rotteveel wrote:
> On Fri, 26 Jul 2013 13:14:08 +0100, Alan J Davies
> <Alan.Davies@...
> <mailto:Alan.Davies%40aldis-systems.co.uk>> wrote:
> > Ok, Mark & Markus, thanks again for both your help.
> > I've tried this construct but get an error -206 column unknown pjs_no_in
> > which is passed in as an input parameter
> > declare variable table_to_use varchar(30);
> > declare variable stmnt varchar(500);
> > begin
> > table_to_use='q_tool';
> > begin
> > EXECUTE STATEMENT 'update '|| table_to_use ||
> > ' set tgp_no=:tgp_no,del_date=:del_date
> > where (pjs_no=:pjs_no_in)';
> >
> > Column does not belong to referenced table.
> > Dynamic SQL Error.
> > SQL error code = -206.
> > Column unknown.
> > PJS_NO_IN.
>
> You are not passing any parameters into the EXECUTE STATEMENT, so it does
> not know about pjs_no_in, del_date or tgp_no. The query executed cannot
> access parameters or variables declared in the stored procedure, you need
> to pass them explicitly. See
> http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-psql-execstat.html#langrefupd25-psql-execstat-with-params
>
> See also my first mail for an example on how you pass in parameters.
>
> Mark
>
>