sql - How to Limit CTE Recursion Depth but Select Generic Table? -

sql - How to Limit CTE Recursion Depth but Select Generic Table? -

currently have stored procedure returns info table in it's original schema doing this:

with cte ( -- start cte off selecting id provided stored procedure. select * [dbo].[testtable] [id] = 1 -- recursively add together tasks children of records found in previous iterations. union select t.* [dbo].[testtable] t inner bring together cte tcte on t.[parentid] = tcte.[id] ) select * cte

this nice, because no matter how table schema changes, long there [id] , [parentid] columns, won't have update stored procedure. i'd similar, able specify depth of recursion dynamically. way i've seen add together level/depth identifier so:

with cte ( -- start cte off selecting task provided stored procedure. select *, 0 [level] [dbo].[testtable] [id] = 1 -- recursively add together tasks children of parent tasks have been found in previous iterations. union select t.*, [level] + 1 [dbo].[testtable] t inner bring together cte tcte on t.[parentid] = tcte.[id] [level] < 2 ) select * cte

this works well, takes away major plus of previous query since selecting * @ end give me level well. there other way of doing specify level, generically select columns table? in advance.

if want level field limit number of recursions, should able utilize maxrecursion query hint, this:

with department_cte ( select departmentgroupkey, parentdepartmentgroupkey, departmentgroupname dimdepartmentgroup departmentgroupkey = 2 union select child.departmentgroupkey, child.parentdepartmentgroupkey, child.departmentgroupname department_cte parent bring together dimdepartmentgroup kid on parent.parentdepartmentgroupkey = child.departmentgroupkey ) select * department_cte alternative (maxrecursion 2)

edit:

in reply question in comments, no, can't not error when using maxrecursion. if understand correctly, this:

with cte ( -- start cte off selecting task provided stored procedure. select id, 0 [level] [dbo].[testtable] [id] = 1 -- recursively add together tasks children of parent tasks have been found in previous iterations. union select t.id, [level] + 1 [dbo].[testtable] t inner bring together cte tcte on t.[parentid] = tcte.[id] [level] < 2 ), cte2 ( select testtable.* cte inner bring together testtable on cte.id = testtable.id ) select * cte2;

this should as generic have above, assuming you're not planning on changing hierarchical or primary key fields.

sql sql-server sql-server-2005 common-table-expression