Join Header Record, Detail Record and create Trailer Record

IMHO, you need to return 3 recordsets. Because you have 3 completely different sets of fields you want to return, you realistically shguldn't try to return all 3 as one recordset as it's more complicated (you'd have to generically name each field in the recordset i.e. Field 1, Field 2, Field 3 etc and each record would need an identifier field to describe what data is in that record (e.g. HEADER, DETAIL, TRAILER). This in turn would mean you would have to have special handling of the recordset along the lines of:

If identifier field = HEADER then FIeld1 = DTYPE, Field2 = POLNUM etc
else if identifier field = DETAIL then Field1 = PARTYP, PARTNO etc
etc

So, I'd recommend 3 different stored procs, each taking a RISR value as a parameter.
CREATE PROCEDURE prGetHeader
    @RISNR INTEGER
AS
SET NOCOUNT ON

SELECT RISNR,DTYPE,CLIENT,POLNUM,CSTREF,TITLE,INITS,SURNAM,ADD1,ADD2,ADD3,ADD4,TOTCST
FROM HeaderTable
WHERE RISNR = @RISNR
GO


CREATE PROCEDURE prGetDetail
    @RISNR INTEGER
AS
SET NOCOUNT ON

SELECT PARTYP,PARTNO,PDSC1,QTYUSD
FROM DetailTable
WHERE RISNR = @RISNR
GO


CREATE PROCEDURE prGetDetail
    @RISNR INTEGER
AS
SET NOCOUNT ON

SELECT T, COUNT(1) AS NoOfHeaderRecords, SUM(TOTCST) AS TOTCSTSum
FROM HeaderTable
WHERE RISNR = @RISNR
GO

You prob may not even need the third procedure as the record count/SUM could be worked out in the front end.

Hope this helps

Thanks - So I made the 3 Proc, it makes sense

Field1 = H(Header), P(Detail), T(Trailer)
Fied2 = RISNR

I did the
-Header Table like
Field1, Field2,Field3,...Field20
H, BA1234, 123,...., 60.00
H, BY1234, 123,...., 66.00

-Detail - Table
Field1, Field2,Field3
P, BA1234, part1, 10.00
P, BA1234, part2, 20.00
P, BA1234, part3, 30.00
P, BY1234, part1, 11.00
P, BY1234, part2, 22.00
P, BY1234, part3, 33.00

Trailer

T, Count(NOofHEADERrec), Sum(Field20)
T,2,126.00

How get i the result like
H, BA1234, 123,...., 60.00
P, BA1234, part1, 10.00
P, BA1234, part2, 20.00
P, BA1234, part3, 30.00
H, BY1234, 123,...., 66.00
P, BY1234, part1, 11.00
P, BY1234, part2, 22.00
P, BY1234, part3, 33.00
T,2,126.00

Klaus

You can't return a recordset exactly like that because header/details/trailer recordsets all have different numbers of fields.

What I was actually saying was call these 3 procedures separately from your front end and handle the display there.

Perhaps you can explain what you want to do with the data?

Thank for your quick answer:
yes, i know!!
Is it Possible to make a Header1, Detail1, Trailer1 Table with 1 Field like:
Header1.Field1 = header.field1 & ";" & header.field2 & ";" ....
Detail1.field1 = detail.field1 & ";" & detail.field2 & ";" ....
Trailer1.field1 = trailer.field1 & ";" & Trailer.field2 & ";" ....

Select Field1 from Header1
Union all
Select Field1 from Detail1
Union all
Select Field1 From Trailer1

But i need for an export - file (smicolom as separator) with following result to send it to a Company

H; BA1234; 123;....; 60.00
P; BA1234; part1; 10.00
P; BA1234; part2; 20.00
P; BA1234; part3; 30.00
H; BY1234; 123;....; 66.00
P; BY1234; part1; 11.00
P; BY1234; part2; 22.00
P; BY1234; part3; 33.00
T;2;126.00

Thanks for your efforts :-))