Can I use recursion in a Sql Server 2005 view?

Question

I tried using OPTION (MAXRECURSION 0) to create a list of dates. This seems unsupported. Is there any workaround?

EDIT to Explain what I really want to do:

I have 2 tables.

table1: int weekday, bool

table2: date-date, bool

I want to get the result: view1: date (all days here this year are here), available (from table2 or from table 1, if not in table2).

This means that I have to apply the date attachment on a weekday. I hope this explanation is understandable, because I actually use more tables with more fields in the query.

I found this code to generate recursion:

 WITH Dates AS ( SELECT cast('2008-01-01' as datetime) Date UNION ALL SELECT Date + 1 FROM Dates WHERE Date + 1 < DATEADD(yy, 1, GETDATE()) ) 

Answer 1

No - if you can find a way to do this within 100 levels of distraction (have a table of numbers), which will be up to 100 levels of recursion, you can do it. But if you have numbers or a pivot table, you still won't need recursion ...

See this question (but I would create a table, not a table function), this question and this link and this link p>

Answer 2

You can use CTE for hierarchical queries.

Answer 3

Here you go:

 ;WITH CTE_Stack(IsPartOfRecursion, Depth, MyDate) AS ( SELECT 0 AS IsPartOfRecursion ,0 AS Dept ,DATEADD(DAY, -1, CAST('01.01.2012' as datetime)) AS MyDate UNION ALL SELECT 1 AS IsPartOfRecursion ,Parent.Depth + 1 AS Depth --,DATEADD(DAY, 1, Parent.MyDate) AS MyDate ,DATEADD(DAY, 1, Parent.MyDate) AS MyDate FROM ( SELECT 0 AS Nothing ) AS TranquillizeSyntaxCheckBecauseWeDontHaveAtable INNER JOIN CTE_Stack AS Parent --ON Parent.Depth < 2005 ON DATEADD(DAY, 1, Parent.MyDate) < DATEADD(YEAR, 1, CAST('01.01.2012' as datetime)) ) SELECT * FROM CTE_Stack WHERE IsPartOfRecursion = 1 OPTION (MAXRECURSION 367) -- Accounting for leap-years ;