tualmente, temos um procedimento armazenado que retorna dados de uma tabela em seu esquema original, fazendo algo assim:

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

Isso é legal, porque não importa como o esquema da tabela seja alterado, desde que haja colunas [Id] e [ParentId], não precisarei atualizar este procedimento armazenado. Gostaria de fazer algo semelhante, mas também poder especificar a profundidade da recursão dinamicamente. A única maneira que eu vi fazer isso é adicionar um identificador de nível / profundidade da seguinte forma:

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

Isso funciona bem, mas retira a vantagem principal da consulta anterior desde a seleção de* no final me dará o nível também. Existe alguma outra maneira de fazer isso em que eu poderia especificar um nível, mas também selecionar genericamente todas as colunas da tabela? Desde já, obrigado