Hi,

You might want to try to put the SUSPEND inside the DO..
BEGIN..END;


With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird & MS SQL Server.
Upscene Productions
http://www.upscene.com

> Can anyone see my obvious mistake in this SP?
>
> Running the query portion in ISQL returns this data:
> 09/24/2003, 4.98
> 09/16/2003, 6.38
> 09/16/2003, 6.38
> 09/17/2003, 10.24
> 09/25/2003, 11.14
>
> etc...
>
> but when I run the SP via "SELECT * FROM drv_hours(:DrvID)" it returns
> null for all three values. I've been trying everything but the right
> thing.
>
> <pre>
> ALTER PROCEDURE DRV_HOURS( DRVID VARCHAR( 4 ) )
>
> RETURNS ( CYCLE7 DECIMAL ( 9, 2 )
> , CYCLE8 DECIMAL ( 9, 2 )
> , CYCLE14 DECIMAL ( 9, 2 ) )
>
> AS
> declare variable DDate Date;
> declare variable CTime decimal ( 9, 2);
>
> BEGIN
>
> FOR SELECT d.dumpdate, h.cycletime
> FROM disp_leg d
> join trk_hauls h on h.haulid = d.haulid
> WHERE dumped = 'Y'
> AND drvid = :DrvID
> AND NOT d.HaulID is null
> AND dumpdate between (current_date - 14) and current_date
> into :DDate, :CTime
> do
> begin
> CYCLE14 = CYCLE14 + :CTime;
> if (DDate >= (Current_Date - 8 )) then
> CYCLE8 = CYCLE8 + :CTime;
> if (DDate >= (Current_Date - 7 )) then
> CYCLE7 = CYCLE7 + :CTime;
> end
> SUSPEND;
> END
> </pre>
>
> Cheers.