Stored procedure problem...

[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

 

[synapsevampire]

Whatever you select last, is what you'll get.

Consider creating mulitple Views to support these multiple data sets, then you can join the Views within CR.

Views should give close to the same performance in this case, and lend themselves better to maintenance and reusability.

-k