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.
Alan J Davies
Aldis
On 26/07/2013 11:36, Markus Ostenried wrote:
> On Fri, Jul 26, 2013 at 12:26 PM, Alan J Davies <
> [email protected]
> <mailto:Alan.Davies%40aldis-systems.co.uk>> wrote:
>
> > **
> >
> >
> > Thanks Mark, it appears that what I want to do is not possible. i.e.
> > have the table name as a replaceable parameter. Using your example I
> > would still have 3 separate statements (as now) but in a different
> > format in the SP.
> > What I really would like to be able to do is (paraphrase):
> > declare table_to_use varchar(20);
> > if my_input_parameter='T' then table_to_use='q_tool'
> > else if my_input_parameter='G' then table_to_use='q_gauge'
> > else if my_input_parameter='P' then table_to_use='q_ppap'
> > end;
> > and then this code only once in the SP.
> >
> > s = 'update :table_to_use 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);
> >
> > Regards
> >
> > Alan
> >
>
> Your "s" is just a string. You don't have to use parameters you can
> concatenate it like this:
>
> s = 'update ' || table_to_use || ' set.....';
>
> HTH,
> Markus
>
> [Non-text portions of this message have been removed]
>
>
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !
Also search the knowledgebases at http://www.ibphoenix.com
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
[email protected]
[email protected]
<*> To unsubscribe from this group, send an email to:
[email protected]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
