Thank you Markus

I used the following and it worked a treat :


CREATE PROCEDURE NEW_ACADEMIC_PROC (STUDENT_ID INTEGER, ACADEMIC_YEAR_ID
INTEGER)
RETURNS (END_DATE DATE, START_DATE DATE)
AS
DECLARE VARIABLE SF_START_DATE DATE;
DECLARE VARIABLE TE_START_DATE DATE;
DECLARE VARIABLE SF_END_DATE DATE;
DECLARE VARIABLE TE_END_DATE DATE;
BEGIN
SELECT
MAX(TE.END_DATE), MIN(TE.START_DATE), SFR.START_DATE, SFR.END_DATE
FROM
STUDENT_FORM_RUN SFR,
FORM_RUN FR,
TIMETABLE TT,
TERM TE
WHERE
SFR.STUDENT_ID = :STUDENT_ID
AND SFR.FORM_RUN_ID = FR.FORM_RUN_ID
AND FR.TIMETABLE_ID = TT.TIMETABLE_ID
AND TT.ACADEMIC_YEAR_ID = :ACADEMIC_YEAR_ID
AND TT.TIMETABLE_ID = TE.TIMETABLE_ID
GROUP BY 3,4
INTO
:TE_END_DATE, :TE_START_DATE, :SF_START_DATE, :SF_END_DATE;
END_DATE = COALESCE(:SF_END_DATE, :TE_END_DATE);
START_DATE = COALESCE(:SF_START_DATE, :TE_START_DATE);
END!!



--- In firebird-support@yahoogroups.com, Markus Ostenried
<chef_007@g...> wrote:

> 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