Navigation

	Home Page
	Index
	All Pages

Quick Search
Advanced Search »

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

Setting Maximum Recursions in a Common Table Expression - SQL Server

By default, recursive common table expressions will recurse to a maximum of 100 times. You can adjust this limit by way of the OPTION (MAXRECURSION n) clause, as follows. Permitted values of n are from 10 to 32767, or 0 for unlimited recursion.

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