sql server - recursive UDF inaccurate in SSRS with embedded SQL -

i creating report in report designer. dataset uses query looks like:

select le.amount, dbo.fnmoneytoenglish(le.amount) tblxxx le le.id = @id 

the udf fnmoneytoenglish should return words like:

thirty , 00/100 dollars

but instead returns

thirty

i suspect because udf uses recursion, calling itself. when execute embedded sql in sql query window udf returns full "thirty , 00/100 dollars". it's report designer has trouble, returning "thirty". find odd because expect ssrs make 1 call database, udf execute , recurse.

does report designer somehow decompose query , limited 1 call database?

fyi, here udf:

alter function [dbo].[fnmoneytoenglish](@money money)   returns varchar(1024)   begin  declare @number bigint  declare @minusflag bit  if @money < 0  begin  set @money = -1 * @money  set @minusflag = 1  end  set @number = floor(@money)  declare @below20 table (id int identity(0,1), word varchar(32))  declare @below100 table (id int identity(2,1), word varchar(32))  insert @below20 (word) values ( 'zero')  insert @below20 (word) values ('one')  insert @below20 (word) values ('two')  insert @below20 (word) values ('three')  insert @below20 (word) values ('four')  insert @below20 (word) values ('five')  insert @below20 (word) values ('six')  insert @below20 (word) values ('seven')  insert @below20 (word) values ('eight')  insert @below20 (word) values ('nine')  insert @below20 (word) values ('ten')  insert @below20 (word) values ('eleven')  insert @below20 (word) values ('twelve')  insert @below20 (word) values ('thirteen')  insert @below20 (word) values ('fourteen')  insert @below20 (word) values ('fifteen')  insert @below20 (word) values ('sixteen')  insert @below20 (word) values ('seventeen')  insert @below20 (word) values ('eighteen')  insert @below20 (word) values ('nineteen')   insert @below100 (word) values ('twenty')  insert @below100 (word) values ('thirty')  insert @below100 (word) values ('forty')  insert @below100 (word) values ('fifty')  insert @below100 (word) values ('sixty')  insert @below100 (word) values ('seventy')  insert @below100 (word) values ('eighty')  insert @below100 (word) values ('ninety')   declare @english varchar(1024)  (  select @english =  case   when @number = 0 ''  when @number between 1 , 19   (select word @below20 id=@number)  when @number between 20 , 99  -- sql server recursive function   (select word @below100 id=@number/10)+ '-' +  dbo.fnmoneytoenglish( @number % 10)   when @number between 100 , 999   (dbo.fnmoneytoenglish( @number / 100))+' hundred '+  dbo.fnmoneytoenglish( @number % 100)   when @number between 1000 , 999999   (dbo.fnmoneytoenglish( @number / 1000))+' thousand '+  dbo.fnmoneytoenglish( @number % 1000)   when @number between 1000000 , 999999999   (dbo.fnmoneytoenglish( @number / 1000000))+' million '+  dbo.fnmoneytoenglish( @number % 1000000)   when @number between 1000000000 , 999999999999   (dbo.fnmoneytoenglish( @number / 1000000000))+' billion '+  dbo.fnmoneytoenglish( @number % 1000000000)   else ' invalid input' end  )  select @english = rtrim(@english)  select @english = rtrim(left(@english,len(@english)-1))  right(@english,1)='-'  if @@nestlevel = 1  begin  select @english = @english+' , ' select @english = @english+     convert(varchar, right('00'+ convert(varchar, convert(int,100*(@money - @number))),2)) +'/100 dollars'  if (@minusflag = 1)  begin  set @english = '( ' + @english + ' )'  end  end  return (@english)  end