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;
START_DATE = COALESCE(:SF_START_DATE,:TE_START_DATE);
END_DATE = COALESCE(:SF_END_DATE,:TE_END_DATE);
SUSPEND;
END !!