>Hello Everyone,
>
>I have a sql select statement that calls user defined functions (either a program procedure or DBC stored procedure) and have noticed that the UDF is called multiple times for each record returned.
>
>In following simplest case, the select statement (table with one record) should produce nSeed = 10, but the output is 20.
>
>

>CLEAR ALL
>PUBLIC pnSeed
>
>pnSeed = 1
>
>SELECT  getnumber() as nSeed FROM appkg!one_record_dummy
>
>PROCEDURE getnumber
>	LOCAL lnRetVal
>	
>	lnRetVal = 10 * pnSeed
>	pnSeed   = pnSeed + 1
>	
>	RETURN lnRetVal
>ENDPROC
>

>
>Does anyone know what's causing this and is there a solution (other than not using UDF's with SQl Select Statements)?
>
>Thanks,
>
>Gary Pike

You can use UDF, but it is simple with a deterministic UDF ( the UDF has not memory ) only.

With a not deterministic UDF the task it is not simple,
this because VFP open an issue and a bug:
issue: VFP call the UDF one time for get the datatype
bug: with a complex exp, the VFP's exp engine have a bug, and recall the UDF another time
Of course, these calls corrupt the UDF memory,
and then you have to rewrite the UDF with a internal workaround.

Example:

CLOSE TABLES ALL
CLEAR 
PUBLIC pnSeed

CREATE CURSOR one_record_dummy (AA I)
APPEND BLANK

pnSeed = 0

? "simple value: getnumber1() ... parser call one time"

SELECT  getnumber1() as nSeed FROM one_record_dummy


pnSeed = 0
? "complex expression: getnumber2()+0 ... parser call two times"
SELECT  getnumber2()+0 as nSeed FROM one_record_dummy

PROCEDURE getnumber1
	pnSeed = m.pnSeed + 1
	? "CALL",m.pnSeed
	
	RETURN IIF(m.pnSeed=1;
		,'12' ; && SQL PARSER EVAL getnumber1's datatype
		,'ABC')  && DATA EVAL
ENDPROC

PROCEDURE getnumber2
	pnSeed = m.pnSeed + 1
	? "CALL",m.pnSeed
	RETURN IIF(m.pnSeed=1 , 1 ; && PARSER EVAL getnumber2()+0
		,  IIF(m.pnSeed=2 , ""; && PARSER EVAL getnumber2() and discard the value !!! BUG
			, 33))  			&& DATA EVAL
ENDPROC