Muthu,

> Here is my SP.
>
> *********************************
>
> CREATE OR ALTER PROCEDURE ZOUT_LIST(
> STARTDATE TIMESTAMP,
> ENDDATE TIMESTAMP)
> RETURNS (
> REPORTSTARTDATE DATE,
> REPORTENDDATE DATE,
> STARTRECEIPT INTEGER,
> ENDRECEIPT INTEGER,
> RECEIPTCOUNT INTEGER,
> TAXABLESALES DECIMAL2,
> NONTAXABLESALES DECIMAL2,
> TAX DECIMAL2,
> SALESRETURN DECIMAL2,
> SALESRETURNCOUNT INTEGER,
> DISCOUNT DECIMAL2,
> TENDERTYPE SMALLINT,
> PAYMENTNAME VARCHAR(20),
> PAYMENTAMOUNT DECIMAL2,
> PAYMENTCOUNT INTEGER)
> AS
> BEGIN
> REPORTSTARTDATE = STARTDATE;
> REPORTENDDATE = STARTDATE + 1;
> FOR SELECT STARTRECEIPT,ENDRECEIPT,RECEIPTCOUNT,TAXABLESALES,
>
> NONTAXABLESALES,TAX,SALESRETURN,SALESRETURNCOUNT,DISCOUNT,TEN
> DERTYPE,
> PAYMENTNAME,PAYMENTAMOUNT,PAYMENTCOUNT
> FROM ZOUTPERIOD(:REPORTSTARTDATE,:REPORTENDDATE)
> WHERE :REPORTSTARTDATE <= :ENDDATE
> INTO :STARTRECEIPT,:ENDRECEIPT,:RECEIPTCOUNT,:TAXABLESALES,
>
> :NONTAXABLESALES,:TAX,:SALESRETURN,:SALESRETURNCOUNT,:DISCOUNT,:
> TENDERTYPE,
> :PAYMENTNAME,:PAYMENTAMOUNT,:PAYMENTCOUNT
> DO
> BEGIN
> REPORTSTARTDATE = REPORTSTARTDATE + 1;
> REPORTENDDATE = REPORTSTARTDATE + 1;
> SUSPEND;
> END
> END

You don't have the ZOUTPERIOD in a loop. The ZOUTPERIOD SP will only be called once, with the initial parameter values.

You need to use the following approach:

...
WHILE (ReportEndDate <= EndDate) DO
BEGIN
FOR SELECT ....
INTO ....
DO
BEGIN
SUSPEND;
REPORTSTARTDATE = REPORTSTARTDATE + 1;
REPORTENDDATE = REPORTSTARTDATE + 1;
END
END
...


Sean