Scalar Valued Function not responding fast when written in Select Query in SQL

Question

Thanks in Advance for reading this question and giving your valuable answers.

Coming to the point...

I have written an sql scalar valued function which consists of a temp table and a simple while loop. The function is as follows.

function name dbo.udfnGetEmpActualPayRate

declare @TotalDeals int               
declare @count int=1                      
declare @EmpRate varchar(Max)=''          
declare @DealTemp table (              
           sno int,                 
           HrsWorked int,              
           rate money              
         )  
delete from @DealTemp         

insert into @DealTemp 
select ROW_NUMBER()OVER (ORDER BY inv.consultantrate)
,SUM(td.changedhours)
,inv.ConsultantRate 
from Invoice_Transactions_tbl inv          
inner join Payroll_Details_tbl pd on pd.TimesheetDetailId=inv.TimeSheetDetailId          
inner join TimeSheet_Detail_tbl td on td.TimeSheetDetailId=pd.TimesheetDetailId          
where pd.PayrollId=@PayrollId and inv.ConsultantId=@EmpId          
group by inv.ConsultantRate          

set @TotalDeals=(select count(distinct inv.ConsultantRate) from Invoice_Transactions_tbl inv          
     inner join Payroll_Details_tbl pd on pd.TimesheetDetailId=inv.TimeSheetDetailId          
     inner join TimeSheet_Detail_tbl td on td.TimeSheetDetailId=pd.TimesheetDetailId          
     where pd.PayrollId=@PayrollId and inv.ConsultantId=@EmpId)

while(@count<=@TotalDeals)          
 begin          
  if(@EmpRate!='')          
   begin          
    set @EmpRate=@EmpRate+' , '          
   end          
  set @EmpRate=@EmpRate+(select convert(varchar(30), rate, 1)+' Rate for Hours '+convert(varchar(30), HrsWorked, 1) from @DealTemp where sno=@count)          
  set @count=@count+1          
 end          

if(@EmpRate='')    
 begin             
 set @EmpRate='0.00 Rate for Hours 0'    
 end    

return @EmpRate   

While I was passing the parameters to this function manually then the function returns the data less than 1 sec

but when I was using the same function in a select query then it takes long time to return the value.

The following 2 images represents the response of the select query before and after using the Function

BEFORE USING

AFTER USING

Can any one help me to figure out where the issue is