+ Reply to Thread
Results 1 to 2 of 2

Thread: Limit CTE Recursion

  1. #1
    Junior Member
    Join Date
    Jan 2012
    Posts
    34

    Limit CTE Recursion

    Currently we have a stored procedure that returns data from a table in it's original schema by doing something like this:
    Code:
    WITH CTE AS
    (
        SELECT *
        FROM [dbo].[TestTable]
        WHERE [Id] = 1
        -- Recursively add tasks that are children of records already found in previous iterations.
        UNION ALL
        SELECT t.*
        FROM [dbo].[TestTable] as t
        INNER JOIN CTE as tcte
            ON t.[ParentId] = tcte.[Id]
    )           
    SELECT * FROM CTE
    This is nice, because no matter how the table schema changes, as long as there are [Id] and [ParentId] columns, I won't have to update this stored procedure. I'd like to do something similar, but also be able to specify the depth of the recursion dynamically. The only way I've seen to do this is to add a Level/Depth identifier like so:
    Code:
    WITH CTE AS
    (
        -- Start CTE off by selecting the task that was provided to stored procedure.
        SELECT *, 0 as [Level]
        FROM [dbo].[TestTable]
        WHERE [Id] = 1
    
        UNION ALL
        SELECT t.*, [Level] + 1
        FROM [dbo].[TestTable] as t
        INNER JOIN CTE as tcte
            ON t.[ParentId] = tcte.[Id]
        WHERE [Level] < 2
    )           
    SELECT * FROM CTE
    This works well, but takes away the major plus of the previous query since selecting * at the end will give me the level as well. Is there some other way of doing this where I could specify a level, but also generically select all columns from the table? Thanks in advance.

  2. #2
    Junior Member
    Join Date
    Jan 2012
    Posts
    41
    If all you want to do with your level field is limit the number of recursions, you should be able to use a MAXRECURSION query hint, something like this:
    Code:
    WITH Department_CTE AS
    (
        SELECT
            DepartmentGroupKey,
            ParentDepartmentGroupKey,
            DepartmentGroupName
        FROM dimDepartmentGroup
        WHERE DepartmentGroupKey = 2
        UNION ALL
        SELECT
            Child.DepartmentGroupKey,
            Child.ParentDepartmentGroupKey,
            Child.DepartmentGroupName
        FROM Department_CTE AS Parent
            JOIN DimDepartmentGroup AS Child
                ON Parent.ParentDepartmentGroupKey = Child.DepartmentGroupKey
    )
    SELECT * FROM Department_CTE
    OPTION (MAXRECURSION 2)
    Edit:
    In answer to the question in the comments, no, you can't not error when using MAXRECURSION. If I understand you correctly, you could do something like this:
    Code:
    WITH CTE AS
    
        SELECT Id, 0 as [Level]
        FROM [dbo].[TestTable]
        WHERE [Id] = 1
        UNION ALL
        SELECT t.Id, [Level] + 1
        FROM [dbo].[TestTable] as t
        INNER JOIN CTE as tcte
            ON t.[ParentId] = tcte.[Id]
        WHERE [Level] < 2
    ),
    CTE2 AS
    (
        SELECT TestTable.* FROM CTE INNER JOIN TestTable ON CTE.Id = TestTable.Id
    )
    SELECT * FROM CTE2;
    This should be equally as generic as what you have above, assuming you're not planning on changing the hierarchical or primary key fields.

+ Reply to Thread

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts