How to Limit CTE Recursion Depth but Select Generic Table?

Question

Currently we have a stored procedure that returns data from a table in it's original schema by doing something like this:

WITH CTE AS
(
    -- Start CTE off by selecting the id that was provided to stored procedure.
    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:

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
    -- Recursively add tasks that are children of parent tasks that have already been found in previous iterations.
    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.

Answer 1

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:

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 suppress the error that you get when recursing more times than your MAXRECURSION setting allows. If I understand you correctly, you could do something like this:

WITH CTE AS
(
    -- Start CTE off by selecting the task that was provided to stored procedure.
    SELECT Id, 0 as [Level]
    FROM [dbo].[TestTable]
    WHERE [Id] = 1
    -- Recursively add tasks that are children of parent tasks that have already been found in previous iterations.
    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.