--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@t...> wrote:

> >CREATE PROCEDURE TU(TUID DECIMAL (15, 0))
> >RETURNS ( SOID DECIMAL (15, 0),NAME VARCHAR (33))
> >AS
> >BEGIN
> > for select
> > s."SoID",
> > s."Name"
> > from TUR t
> > join PRO p
> > on t."ProID" = p."ProID"
> > join AM_KL ak
> > on p."AKID" = ak."AKID"
> > join KLA k
> > on ak."KlaID" = k."KlaID"
> > join KL_SO ks
> > on k."KlaID" = ks."KlaID"
> > join SO s
> > on ks."SoID" = s."SoID"
> > where t."TuID" = :TuID and not exists ( select 1 from TU_SO
> >where "TuID" = :TuID and "SoID" = s."SoID" )
> > into :SoID, :Name
> > do
> > SUSPEND;
> >end
>
> OK, there are two problems with this procedure. 1) it needs to have

TUID

> in its output set.and 2) you have ambiguity in the WHERE clause.

Fix this

> up so that it is like this:
> where t."TuID" = :TuID
> and not exists ( select 1 from TU_SO ts
> where ts."TuID" = :TuID and ts."SoID" = s."SoID" )

Got it, question about 1) further

> >DeleteSQL calling code:
> >// TuID is already provided through the MasterParamsLinks
>
> No, it isn't. You can't masterlink on a non-existent output column.
>
> >qry->ParamByName("SoID")->Assign(qry2->FieldByName("SoID"));
>
> No. This is the wrong thing to do when the DeleteSQL is bound to the
> dataset. The Params belong to the SELECT query, not to the bound

DeleteSQL

> statement. The query doesn't have a Params member named "SoID".

Am I right, that SELECT query has it own Params, DeleteSQL its own
too, InsertSQL - also own Params? I thought that IBO has one shared
Params for all needs.

> I asked you to provide the DeleteSQL statement, but you didn't.

With your

> Delete SP as written, it should be as follows:
>
> EXECUTE PROCEDURE DELTU ( :TUID, :SOID )
>
> and you do *not* assign the parameter values: IBO does this

internally -

> they are not available to the application. The important thing is

for you

> to match the param names in the EXECUTE PROCEDURE statement

*exactly* to

> the output field names of the SELECT procedure.

DeleteSQL statement looks (I mentioned it in the first letter)
execute procedure DelTu(:TuID,:SoID)

Important question is: why SELECT output fields must match EXECUTE
PROCEDURE (DeleteSQL in my case) arguments? In my case TuID would be
the same for all SPP output fields and this field value is known
before select. Why to include TuID - this increases data amount from
the server to client (first what comes into the mind - this is
required by the IBO architecture and needed for internal work) and not
always needed by the user (as in my case)?
If SELECT and DeleteSQL has their own Params, why SELECT output
dataset would match DeleteSQL Params?
Hope that I don't ask dumb questions :)

> Provided you have this (with no mismatches involving quoted field
> identifiers), just removing that assignment statement should fix the
> problem of the "unkown field name".