Cursors in a dbfunction

[Alessandro Cristiani]

I created this function:

CREATE DBFUNCTION RISORSA_OPZIONATA(CODRISORSA FIXED(10,0), CODESIGENZA
FIXED(10,0)) RETURNS fixed
AS

    VAR TMPVAR FIXED; TMPVAR1 FIXED; PARAM FIXED; RISULTATO VARCHAR;

    SET TMPVAR = 0; TMPVAR1 = 100; PARAM = 0;

    IF CODESIGENZA IS NOT NULL THEN
        BEGIN
            DECLARE SELECT_ESIGENZA CURSOR FOR
                SELECT COUNT(*)
                  FROM CAST.OPZIONI
                 WHERE FKIDRISORSA = :CODRISORSA
                 AND FKIDESIGENZA = :CODESIGENZA
                  AND NOW() < DDATOPZIONEAL;
            FETCH SELECT_ESIGENZA INTO :TMPVAR;
            CLOSE SELECT_ESIGENZA;
        END;

                SELECT COUNT(*) into :tmpvar1
                  FROM CAST.OPZIONI
                 WHERE FKIDRISORSA = :CODRISORSA
                   AND NOW() < DDATOPZIONEAL;

            IF TMPVAR1 > 0 THEN
                SET RISULTATO = 'S'
            ELSE
                SET RISULTATO = 'N';

            IF TMPVAR > 0 THEN
                SET RISULTATO = 'O';
 

    RETURN TMPVAR1;

When I execute this function with this statement:
        select RISORSA_OPZIONATA(60677, null)
the result is 1 and this is corrent;
When I execute this function with this statement:
        select RISORSA_OPZIONATA(60677, 107)
the result is 100 and this is not correct, because the 107 value is not
int the table OPZIONI so the result must be 1.

Thank you


Alessandro Cristiani