--- In firebird-support@yahoogroups.com, Thomas Besand wrote:

> Hi NG,
>
> I have not yet dug too deep into writing stored procs, but I'm
> trying right now.
> What I want to do is:
> 1. return all the records of two tables that have identical values
> in certain fields.
> 2. return the remaining records of the two tables, i.e. without the
> records from the above query
> e.g.
> Table A
> fld1
> fld2
> fld3
> fld4
> fld5
> Table B
> fld6
> fld7
> fld8
> fld9
> fld10
>
> 1. Give me all records from A and B where A.fld2=B.fld6 and
> A.fld3=B.fld7 and A.fld4=B.fld9;
> 2. Give me all records from A and B excluding those where
> A.fld2=B.fld6 and A.fld3=B.fld7 and A.fld4=B.fld9;
>
> Can I do that in a stored procedure?
> As I do not know the tablenames and fields beforehand, I was
> thinking about creating and compiling the procedures on the fly. Is
> that viable?

Hi Thomas,
stored procedures are good when you know the names of tables and
fields, but I'd say not very suitable for such a flexible solution as
you are describing. Though my experience is limited in this regard, I
would rather suggest you create simple queries at the client, e.g.

select cast(1 as SmallInt) OrderFld, a.fld1, b.fld1
from a
join b on A.fld2=B.fld6 and A.fld3=B.fld7 and A.fld4=B.fld9
union
select cast(2 as SmallInt), a.fld1, ''
from a
where not exists(select * from b
where A.fld2=B.fld6 and A.fld3=B.fld7 and A.fld4=B.fld9)
union
select cast(3 as SmallInt), '', b.fld1
from b
where not exists(select * from a
where A.fld2=B.fld6 and A.fld3=B.fld7 and A.fld4=B.fld9)
order by 1, 2, 3

HTH,
Set