At 22:16 Wednesday, 02.03.2005 +0000, donoghue_brendan wrote:

>Hello
>
>When I run the following stored procedure (execute procedure
>academic_proc(400,61)) I get a single row with NULL, NULL as the output.
>When I run it with the (select start_date,end_date from
>academic_proc(400,61) I get 2 rows. The top row is NULL, NULL and the 2nd
>row is the row that I am epxecting to see).
>
>Why is this so? Why would I be getting a result at all that is NULL, NULL
>when there is data in at least one of the colums being queried?
>
>I am using firebird 1.5.
>
>Thanks
>
>Brendan Donoghue
>
>
>ALTER PROCEDURE ACADEMIC_PROC (STUDENT_ID INTEGER, ACADEMIC_RUN_ID
>INTEGER)
>RETURNS (START_DATE DATE, END_DATE DATE)
>AS
>DECLARE SF_START_DATE DATE;
>DECLARE TE_START_DATE DATE;
>DECLARE SF_END_DATE DATE;
>DECLARE TE_END_DATE DATE;
>BEGIN FOR
> SELECT
> sfr.start_date, MIN(TE.start_date),
> sfr.end_date, MAX(TE.end_date)
> FROM TIMETABLE TT,
> TERM TE,
> STUDENT_FORM_RUN SFR,
> FORM_RUN FR
> WHERE TE.TIMETABLE_ID = TT.TIMETABLE_ID
> AND TT.ACADEMIC_YEAR_id = :academic_run_id
> AND SFR.FORM_RUN_ID = FR.FORM_RUN_ID
> AND FR.TIMETABLE_ID = TT.TIMETABLE_ID
> AND sfr.student_id = :student_id
> GROUP BY 1,3
> INTO :SF_START_DATE,:TE_START_DATE, :SF_END_DATE, :TE_END_DATE
> DO SUSPEND;

Suspend provides a new row that the client can fetch. Here you call suspend
without having assigned anything to your output variables START_DATE DATE
and END_DATE DATE. They are initialized to NULL so you get "NULL, NULL" back.

>START_DATE = COALESCE(:SF_START_DATE,:TE_START_DATE);
>END_DATE = COALESCE(:SF_END_DATE,:TE_END_DATE);
>SUSPEND;
> END !!

here your stored proc yields a second row, this time with values assigned
to the output variables.
So if you remove the first call to suspend you should be fine.

HTH,
Markus