Tuesday, September 23, 2008

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

No comments: