Jasinski Technical Wiki

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

PoweredBy

Setting Maximum Recursions in a Common Table Expression - SQL Server

RSS
Modified on Tue, Jan 13, 2009, 9:47 AM by Administrator Categorized as 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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.