-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Maya Opperman
Sent: 27. mai 2010 06:53
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Re: Looping a Stored Procedure - Email found in subject

>>I Modified the SP as suggested, but it hangs, I even added suspend for

while loop, but still hangs

WHILE (REPORTENDDATE <= ENDDATE) DO
BEGIN
FOR SELECT STARTRECEIPT,ENDRECEIPT,RECEIPTCOUNT,TAXABLESALES,
<snip>
DO
BEGIN
SUSPEND;
REPORTSTARTDATE = REPORTSTARTDATE + 1;
REPORTENDDATE = REPORTSTARTDATE + 1;
END
-- if you have no records returned by your FOR SELECT statement, your
WHILE loop will never change... hence the "hanging"
END

Hence, what you have to do, Muthu, is to move the assignment to REPORTSTARTDATE and REPORTENDDATE out of the FOR SELECT loop, like this:

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;
WHILE (REPORTENDDATE <= ENDDATE) DO
BEGIN
FOR SELECT STARTRECEIPT,ENDRECEIPT,RECEIPTCOUNT,TAXABLESALES,
NONTAXABLESALES,TAX,SALESRETURN,SALESRETURNCOUNT,DISCOUNT,TENDERTYPE,
PAYMENTNAME,PAYMENTAMOUNT,PAYMENTCOUNT
FROM ZOUTPERIOD(:REPORTSTARTDATE,:REPORTENDDATE)
INTO :STARTRECEIPT,:ENDRECEIPT,:RECEIPTCOUNT,:TAXABLESALES,
:NONTAXABLESALES,:TAX,:SALESRETURN,:SALESRETURNCOUNT,:DISCOUNT,:TENDERTYPE,
:PAYMENTNAME,:PAYMENTAMOUNT,:PAYMENTCOUNT
DO
BEGIN
SUSPEND;
END
REPORTSTARTDATE = REPORTSTARTDATE + 1;
REPORTENDDATE = REPORTSTARTDATE + 1;
END
END

Set