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())
)

I solved the problem by creating an extra table in the database with the date and the dayofweek as columns. I set the millenium bug at 2037 for the next developer at that time... — Paco, Nov 01 '08 at 16:01

score 2 · Accepted Answer · edited May 23 '17 at 12:19

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

edited May 23 '17 at 12:19

Community

1
1

answered Oct 31 '08 at 18:17

Cade Roux

88,164
40
182
265

score 0 · Answer 2 · answered Nov 16 '12 at 13:24

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
;

This is not valid inside a view which is the point of the question. The hint needs to go in the select from the view. — Martin Smith, Nov 16 '12 at 14:24

score 0 · Answer 3 · answered Oct 31 '08 at 16:46

0

You can use a CTE for hierarchical queries.

answered Oct 31 '08 at 16:46

TcKs

25,849
11
66
104

This is CTE or am I missing something? – Paco Oct 31 '08 at 16:58
That is a CTE, but the recursion level is limited to 100 in a view (or earlier, if the recursion terminates naturally with some criteria in the CTE). – Cade Roux Oct 31 '08 at 18:23

Can I use recursion in a Sql Server 2005 View?

3 Answers3