How do I recurse up a multi-level Parent-Child relationship in T-SQL?

I had to change it slight because you had an "ALTER" in there instead of a "CREATE" and you had orderid in the initial create table instead of name (which I changed to subject to fit my needs.  But after those minor modifications it works exactly how I need to it.

However, after reading chapmandew's article (and even though I barely understood it bc I'm not a hardcore SQL guy) he's got me scared about the whole "recursion - blocking" thing.  

chapmandew - do you have a simple example of how to accomplish what you are doing?  I didn't know how the original table should be set up to play around with your example in your article.

CREATE TABLE dbo.childparent (
  id int not null,
  parentid int null,
  Subject varchar(50))
GO
 
insert into childparent (id, parentid, Subject) values (1, null, 'A')
insert into childparent (id, parentid, Subject) values (2, 1, 'A-1')
insert into childparent (id, parentid, Subject) values (3, 1, 'A-2')
insert into childparent (id, parentid, Subject) values (4, 3, 'A-2-a')
insert into childparent (id, parentid, Subject) values (5, null, 'B')
insert into childparent (id, parentid, Subject) values (6, 5, 'B-1')
insert into childparent (id, parentid, Subject) values (7, 6, 'B-1-a')
insert into childparent (id, parentid, Subject) values (8, null, 'C')
GO
 
CREATE procedure dbo.cp(@id int)
as 
begin
  declare @temp table (id int not null, parentid int null, orderid int null)
 
  declare @aid int, @aparentid int, @orderid int
  select @orderid = 1
 
  select @aid = id, @aparentid = parentid from childparent where id = @id
 
  while (@aid is not null)
  begin
    insert into @temp (id, parentid, orderid) values (@aid, @aparentid, @orderid)
 
    if @aparentid is not null
      select @aid = id, @aparentid = parentid, @orderid = @orderid + 1 from childparent where id = @aparentid
    else 
      select @aid  = null
  end
  
  select * from @temp order by orderid
end
GO
 
execute cp 4 
GO

Select allOpen in new window

SAH WEET!!!!

THIS DID THE TRICK!!!  Thanks for the info about the CTEs!

WITH Hierarchy(TaskID, [Subject], ParentID)
AS
(
	SELECT 
		TaskID, 
		[Subject], 
		ParentID 
	FROM 
		PMA_Task 
	WHERE 
		TaskID = 258
	
	UNION ALL
	
	SELECT 
		SubDepartment.TaskID, 
		SubDepartment.[Subject], 
		SubDepartment.ParentID 
	FROM 
		PMA_Task SubDepartment
    INNER JOIN 
		Hierarchy ParentDepartment
    ON 
		SubDepartment.TaskID = ParentDepartment.ParentID 
)
 
SELECT 
	TaskID, 
    [Subject], 
    ParentID
FROM  
	Hierarchy
ORDER BY
	TaskID

Select allOpen in new window

I actually had to add an option to make sure it sorted properly incase the IDs were out of order

WITH Hierarchy(TaskID, [Subject], ParentID, HLevel)
AS
(
	SELECT 
		TaskID, 
		[Subject], 
		ParentID ,
		0 as HLevel
	FROM 
		PMA_Task 
	WHERE 
		TaskID = @TaskID
	
	UNION ALL
	
	SELECT 
		SubDepartment.TaskID, 
		SubDepartment.[Subject], 
		SubDepartment.ParentID ,
		HLevel + 1
	FROM 
		PMA_Task SubDepartment
    INNER JOIN 
		Hierarchy ParentDepartment
    ON 
		SubDepartment.TaskID = ParentDepartment.ParentID 
)
 
SELECT 
	TaskID, 
    [Subject], 
    ParentID,
	HLevel
FROM  
	Hierarchy
ORDER BY
	HLevel DESC

Select allOpen in new window

looks good!