SQL UDF to return an array of dates

Question

I have currently written a small UDF (Table Value Type) to return a table of dates between a start and end date. Start and End dates are already retrieved from another table.

Start_End_Table
WeekNumber | Start Date | End Date

Function:

CREATE FUNCTION [dbo].[DatesBetween](@startDate datetime, @endDate datetime)
RETURNS @dates TABLE (
   DateValue datetime NOT NULL
) 
AS
BEGIN
   WHILE (@startDate <= @endDate) BEGIN
      INSERT INTO @dates VALUES (@startDate);
      SET @startDate = DATEADD(day, 1, @startDate);
   END;

   RETURN;
END;

Until now, I have been using this function in the following manner,

SELECT * FROM [dbo].[DatesBetween](@startDate datetime, @endDate datetime);

Going forward I need to get start, end dates from Start_End_Date table and call the function. Thus I do not think returning a table is an option any longer. How can I get this function to return an array of dates instead of a table? Or is there any other way I could wrap this up?

Answer 1

Try CROSS APPLY as it will run the function for each row in the JOINed table:

SELECT [Date Value]
FROM dbo.Start_End_Table dates
CROSS APPLY [dbo].[DatesBetween](dates.StartDate, dates.EndDate)
WHERE [Week Number]=@WeekNumber;

A sample output would be something like for a start date = 1/1/2014, end date = 1/3/2014, @weeknumber = 1:

 | DateValue
  1/1/2014
  1/2/2014
  1/3/2014

SQL Fiddle

EDIT:
Try the following Inline Table-Valued Function as it will be much faster than your multi-line TVF due to:

1. Inline TVFs are inherently faster than multi-line TVFs
2. It is set-based rather than relying on a WHILE loop

--

CREATE FUNCTION dbo.GetDates(@StartDate DATETIME, @EndDate DATETIME)
RETURNS TABLE
AS RETURN
WITH cte AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS [RowNum]
    FROM sys.objects WITH (NOLOCK)
)
SELECT DATEADD(DAY, (cte.[RowNum] - 1), @StartDate) AS [DateValue]
FROM cte
WHERE cte.[RowNum] < (DATEDIFF(DAY, @StartDate, @EndDate) + 2);
GO

SELECT * FROM dbo.GetDates('1/1/2014', '1/3/2014');