Rewrite this Scalar UDF as a Inline TVF

Question

I am trying to write this scalar UDF:

CREATE FUNCTION [dbo].[DAYSADDNOWK](@addDate AS DATE, @numDays AS INT)
RETURNS DATETIME
AS
BEGIN
    WHILE @numDays>0
    BEGIN
       SET @addDate=DATEADD(d,1,@addDate)
       IF DATENAME(DW,@addDate)='saturday' SET @addDate=DATEADD(d,1,@addDate)
       IF DATENAME(DW,@addDate)='sunday' SET @addDate=DATEADD(d,1,@addDate)
  
       SET @numDays=@numDays-1
    END
  
    RETURN CAST(@addDate AS DATETIME)
END
GO

as an inline TVF.

I have been trying to use CTEs in the TVF to replace the while loop, but I keep running into myriad issues, so if anyone has any ideas and could help I would be incredibly grateful.

Requirements: Take in a date, d, and an integer, i, as parameters and return a date that is that is i many business days (weekdays) from the date, d, argument passed in.

While I appreciate that there may be better ways to go about this, and would love to read them if they're suggested here, I also really would like to know how to accomplish this using recursive CTE(s) in a Inline TVF as I am more doing this as practice so I can apply this technique to more complicated scalar UDF's I may need to refactor in the future.

Answer 1

Converting to a TVF and eliminating the loops is the absolute right thing to do.

As I commented, consider a Calendar Table ... there are many things that can easily be accomplished.

Here is a TVF which uses an ad-hoc tally table to

CREATE FUNCTION [dbo].[YourFunctionName] (@D date,@I int)
Returns Table
Return (

Select WorkDate = D
      ,WorkDays = RN
 From  (
        Select D
              ,RN = -1+row_number() over( order by D)
         From  (
                Select Top ((@I+1)*2) D=dateadd(DAY,-1+Row_Number() Over (Order By (Select NULL)),@D)
                  From master..spt_values n1, master..spt_values n2
               ) A
         Where datename(WEEKDAY,D) not in ('Saturday','Sunday')
       ) A
 Where RN=@I

)