On 08/02/13 16:54, SoftTech wrote:

> I have a stored procedure where I pass in a begin and end date.
>
> Is there way to process each date within a for or while loop so that I can
> return a record for each date regardless if I find any data to report on for
> that date?
>
> Any tips on how to code this would be greatly appreciated.

Something like this might help:

SET TERM ^ ;

CREATE PROCEDURE DATE_RANGE
( START_DATE DATE, END_DATE DATE )
RETURNS
( INTERVAL_DATE DATE )
AS
BEGIN
INTERVAL_DATE = START_DATE;
WHILE (INTERVAL_DATE <= END_DATE) DO
BEGIN
SUSPEND;
INTERVAL_DATE = INTERVAL_DATE + 1;
END
END^

SET TERM ; ^

Which can be used with a query or modified to suit:

/* All dates from 5th Jan to 10th Jan inclusive */
SELECT INTERVAL_DATE FROM DATE_RANGE('1/5/2013', '1/10/2013');

Scott