Can I use recursion in a Sql Server 2005 View?

Question

I tried to use OPTION (MAXRECURSION 0) in a view to generate a list of dates. This seems to be unsupported. Is there a workaround for this issue?

EDIT to Explain what I actually want to do:

I have 2 tables.

table1: int weekday, bool available

table2: datetime date, bool available

I want the result: view1: date (here all days in this year), available(from table2 or from table1 when not in table2).

That means I have to apply a join on a date with 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 the 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

You can use a CTE for hierarchical queries.

Answer 2

No - if you can find a way to do it within 100 levels of recusion (have a table of numbers), which will get you to within 100 recursion levels, you'll be able to do it. But if you have a numbers or pivot table, you won't need the recursion anyway...

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