--- In ib-support@yahoogroups.com, Walter Neumann <walter@b...> wrote:

> Hi,
> I wrote a SP, which worked fine. Then I changed it, and now I

recieve always

> ANZAHL=0. The 1. SP had no input parameters. I used always 'TODAY'

for

> comparison. Here is the new SP:
>
> ALTER PROCEDURE "MITARB_COUNT"
> (
> "STICHTAG2" TIMESTAMP,
> "STICHTAG1" TIMESTAMP
> )
> RETURNS
> (
> "ANZAHL" INTEGER
> )
> AS
> DECLARE VARIABLE MAID INT;
> BEGIN
> ANZAHL = 0;
> FOR SELECT MA.PERSONENIDINT FROM MITARBVIEW MA
> WHERE :STICHTAG2 >= (SELECT MAX(EA.EINTRITTSDATUM) FROM

MITARBEINAUSVIEW

> EA
> WHERE ((EA.MITARBID = MA.PERSONENIDINT)
> AND ((EA.AUSTRITTSDATUM IS NULL) OR (EA.AUSTRITTSDATUM >=
> :STICHTAG1))))
> INTO :MAID
> DO
> BEGIN
> ANZAHL = ANZAHL + 1;
> END
> SUSPEND;
> END
>
> Thank you for your advices.

Walter, firstly perhaps I did'nt understand you about 'today' usage,
but this code can't return anything - both STICHTAG1 and STICHTAG2 are
undefined, nulls, and any comparison with them returns 'false', so
loop body is never performed. Secondly, avoid usage of parameters in
main select and subselect simultaneously, all IB/FB versions (perhaps
except FB1.5, can't recall) can wrongly parse such constructions and
return wrong result.

Best regards, Alexander.