Common Table Expressions : MAXRECURSION

I was looking today to generate a sequential list of dates as quickly as possible.

The fastest solution to the problem seemed to involve a common table expression (CTE) in sql 2005.

CREATE FUNCTION GenerateDates(@startdate DATETIME, @enddate DATETIME)
RETURNS TABLE
AS
RETURN 
(
      WITH DatesCTE(CurrentDate) AS
      (
            SELECT @startdate as CurrentDate
            UNION ALL
            SELECT DATEADD(day,1,CurrentDate)
            FROM DatesCTE
            WHERE CurrentDate < @enddate 
   
      ) 
      SELECT CurrentDate FROM DatesCTE
);

Excellent execution time, but there is a downfall. The function cannot cope with providing a list of more than 100 dates.

Msg 530, Level 16, State 1, Line 2
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

This is because the defalt recursion level for a CTE is 100.

Normally the recursion level can be specified manually to get round this using OPTION (MAXRECURSION limit) where limit is a value from 10 to 32767, or 0 for unlimited recursion.

Setting the recursion level with OPTION (MAXRECURSION limit) is NOT SUPPORTED inside user defined functions and views.

Not as tidy as using a function, but we can do with a stored procedure >

CREATE PROCEDURE dbo.usp_GenerateDates
  @startdate DATETIME ,@enddate DATETIME
AS
BEGIN
;WITH DatesCTE(CurrentDate) AS
  (
    SELECT @startdate AS CurrentDate
    UNION ALL
    SELECT DATEADD(day,1,CurrentDate)
    FROM DatesCTE
    WHERE CurrentDate < @enddate  
  ) 

SELECT CurrentDate FROM DatesCTE
OPTION (MAXRECURSION 0)
END
GO

We can put the output of the procedure into a temporary table like this >

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#tblDateList%')
   DROP TABLE #tblDateList
 
CREATE TABLE #tblDateList
   ( [Date] DATETIME NOT NULL,
     CONSTRAINT [PK_#tblDateList] PRIMARY KEY CLUSTERED ([Date] ASC) 
   ) ON [PRIMARY]
  
DECLARE @startdate DATETIME
DECLARE @enddate DATETIME

SET @enddate   = CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)
SET @startdate = DATEADD(DAY,-100,@enddate)

INSERT INTO #tblDateList
EXECUTE dbo.usp_GenerateDates
  @startdate = @startdate
 ,@enddate = @enddate