Problem with Table-Valued Functions

Question

I created a function to return a value.
When I called the function, for unknown reason (to me, it is unknown), I did not get the result.

Here is how I called the function:

SELECT * FROM cu.fnBIDouangTest('12/28/2020',2);

Here is my function:

ALTER FUNCTION cu.fnBIDouangTest(@SettlementDate DATE,@count int)

RETURNS @FinalFundingDate TABLE (FundingDate DATE)

AS

BEGIN


--Create Holidays temp table    
DECLARE @TblHoliday AS TABLE (Holiday_Date DATE)  
INSERT INTO @TblHoliday --(populating holidays from DimDate table)
    SELECT 
        FullDate Holiday_Date 
    FROM DBO.dimDate d

    WHERE d.IsUSCivilHoliday = 'Y'
        AND YearNum >= 2019


--Running through the loop twice
  DECLARE @FundingDate DATE
  DECLARE @i int = 1; -- my "iterator"
  WHILE @i <= @count
  BEGIN
    SET @FundingDate = dateadd(day,  -1, @Settlementdate)

    WHILE ((SELECT TOP 1 1
                FROM @TblHoliday
                WHERE Holiday_Date = @FundingDate) = 1 OR datename(dw, @FundingDate) IN ('Saturday', 'Sunday'))
        BEGIN
            SET @FundingDate = dateadd(day, - 1, @FundingDate)
        END

        SET @i = @i+1
        SET @Settlementdate = @FundingDate
    END

RETURN;

END

When I run the function as a static query,
example here:

DECLARE @SettlementDate DATE = '12/28/2020'
DECLARE @count INT = 2

BEGIN

--Create Holidays temp table    
DECLARE @TblHoliday AS TABLE (Holiday_Date DATE)  
INSERT INTO @TblHoliday --(populating holidays from DimDate table)
    SELECT 
        FullDate Holiday_Date 
    FROM DBO.dimDate d

    WHERE d.IsUSCivilHoliday = 'Y'
        AND YearNum >= 2019

--Beginning of the loop
  DECLARE @FundingDate DATE
  DECLARE @i int = 1; -- my "iterator"
  WHILE @i <= @count
  BEGIN
    SET @FundingDate = dateadd(day,  -1, @Settlementdate)

    WHILE ((SELECT TOP 1 1
                FROM @TblHoliday
                WHERE Holiday_Date = @FundingDate) = 1 OR datename(dw, @FundingDate) IN ('Saturday', 'Sunday'))
        BEGIN
            SET @FundingDate = dateadd(day, - 1, @FundingDate)
        END

        SET @i = @i+1
        SET @Settlementdate = @FundingDate
    END

END

SELECT @Settlementdate

I get the result as 2020-12-23 which is correct.

But, I just don't see why I don't get the result when I turned the above code into a function.

Thank you in advance for your help.

Answer 1

I assume you are questioning why you get no result at all from your function. That is because you created a function which returns a table, but you didn't populate the table.

You are only returning a single value, you should just use a scalar function.
CREATE OR ALTER FUNCTION dbo.fnBIDouangTest(@SettlementDate DATE,@count int)

 RETURNS DATE

 AS

 BEGIN


 --Create Holidays temp table    
 DECLARE @TblHoliday AS TABLE (Holiday_Date DATE)  
 INSERT INTO @TblHoliday --(populating holidays from DimDate table)
     SELECT 
         FullDate Holiday_Date 
     FROM DBO.dimDate d

     WHERE d.IsUSCivilHoliday = 'Y'
         AND YearNum >= 2019

 --Running through the loop twice
   DECLARE @FundingDate DATE
   DECLARE @i int = 1; -- my "iterator"
   WHILE @i <= @count
   BEGIN
     SET @FundingDate = dateadd(day,  -1, @Settlementdate)

     WHILE ((SELECT TOP 1 1
                 FROM @TblHoliday
                 WHERE Holiday_Date = @FundingDate) = 1 OR datename(dw, @FundingDate) IN ('Saturday', 'Sunday'))
         BEGIN
             SET @FundingDate = dateadd(day, - 1, @FundingDate)
         END

         SET @i = @i+1
         SET @Settlementdate = @FundingDate
     END

 RETURN @FundingDate;

 END

Answer 2

PS. You don't need to loop to get the value you are looking for:

CREATE OR ALTER FUNCTION dbo.udf_SubBusinessDays
    (@Date date, @BusinessDays int)
RETURNS date
AS
BEGIN
      RETURN (
            SELECT TOP (1) FullDate AS BusinessDate
            FROM (SELECT TOP (@BusinessDays) FullDate
                  FROM dbo.dimDate
                  WHERE
                        FullDate < @Date
                        AND IsUSCivilHoliday <> 'Y'
                            AND datename(dw, FullDate) NOT IN ('Saturday', 'Sunday')
                  ORDER BY FullDate DESC) AS BusinessDays
            ORDER BY FullDate ASC
      )
END