How do I call a stored procedure involving 3 result tables?

[shanegarcia]

This is the stored procedure that I'm trying to call, however, when I get the resultset in Crystal it only shows the header table... Any ideas on how I can get the other tables (i.e. details, etc.)

Any inputs would be greatly appreciated...

Thanks....

<STORED PROCEDURE CODE>

if exists (select * from sysobjects where id = object_id(N'[dbo].[purchase_sp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].purchase_sp
go


create procedure [purchase_sp]


@datefrom as char (10),
@dateto as char (10)


as

if exists (select * from sysobjects where id = object_id(N'AOHQ.[dbo].[tmp_HdrPurchase]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].tmp_HdrPurchase

if exists (select * from sysobjects where id = object_id(N'AOHQ.[dbo].[tmp_DetPurchase]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].tmp_DetPurchase

if exists (select * from sysobjects where id = object_id(N'AOHQ.[dbo].[tmp_FtrPurchase]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].tmp_FtrPurchase


------------------

SELECT DATEADD(DD, A.DATE_APPLIED-639906, '1/1/1753') AS DATE_APPLIED, B.ADDRESS_NAME, A.DOC_DESC, A.DOC_CTRL_NUM, A.PO_CTRL_NUM, A.TRX_CTRL_NUM, AMT_NET*rate_home AS AP, AMT_TAX*rate_home AS INPUT_TAX, AMT_DISCOUNT*rate_home AS EWT, DATE_APPLIED AS DD, RATE_HOME, 'AOHQ' AS COMP_NAME
INTO tmp_HdrPurchase
FROM APVOHDR A, APMASTER B
WHERE A.DATE_APPLIED BETWEEN @datefrom AND @dateto AND
A.VENDOR_CODE = B.VENDOR_CODE
ORDER BY A.TRX_CTRL_NUM


SELECT A.TRX_CTRL_NUM, SEQUENCE_ID, (AMT_EXTENDED-CALC_TAX)* C.RATE_HOME AS AMT_EXPENSE, B.DESCRIPTION, GL_EXP_ACCT
INTO tmp_DetPurchase
FROM APVODET A, GLSEG1 B, tmp_HdrPurchase C
WHERE A.TRX_CTRL_NUM IN (SELECT TRX_CTRL_NUM FROM APVOHDR WHERE DATE_APPLIED BETWEEN @datefrom AND @dateto) AND
SUBSTRING(GL_EXP_ACCT,1,8)=B.SEG_CODE AND
A.TRX_CTRL_NUM = C.TRX_CTRL_NUM
ORDER BY A.TRX_CTRL_NUM


SELECT 'AOHQ' AS COMP_NAME, SUM(AP) AS TOT_AP, SUM(INPUT_TAX) AS TOT_INPUTTAX, SUM(EWT) AS TOT_EWT
INTO tmp_FtrPurchase
from tmp_HdrPurchase


select * from tmp_HdrPurchase
select * from tmp_DetPurchase
select * from tmp_FtrPurchase

GRANT ALL ON [dbo].purchase_sp TO PUBLIC
GO

 

[morriskc]

Crystal will only display the first result set it receives from the stored proc. You will need to return a single result set that contains all the data you need. Rename the fields as needed,

e.g.,

instead of
select * from tmp_HdrPurchase
select * from tmp_DetPurchase
select * from tmp_FtrPurchase

use:
Select t1.HDRdate_applied
,t1.HDRaddress_name
,t2.DTLTRX_CTRL_NUM
,t2.DTLSEQUENCE_ID
,t3.FTRCOMP_NAME
,t3.FTRTOT_AP
from tmp_HdrPurchase t1
,tmp_DetPurchase t2
,tmp_FtrPurchase t3

filling in the missing fields as the example shows above.

This will give you all the info you need to create the report.