CODEWITHSUNDEEP
c#sql-server-2008recursion
Rauf
Rauf

Reputation: 12852

recursion-get sequence of parent folders in .Net using recursion

I have a table with the following structure

    ID          Name                                               Parent
----------- -------------------------------------------------- -----------
1           Root                                               NULL
2           Root_A                                             1
3           Root_B                                             1
4           Root_C                                             1
5           Root_C_A                                           4
6           Root_C_A_A                                         5
7           Root_C_A_A_A                                       6

So if I pass 7, I would like to get the following

Root --> Root_C --> Root_C_A --> Root_C_A_A

That means I want to traverse back to root from a child. How can I do it using SQL Server 2008 Stored Procedures or with other .Net techniques ?

I think I can accomplish the task using recursive function

Upvotes: 2

Views: 197

Answers (2)

ojlovecd
ojlovecd

Reputation: 4902

try this:

declare @result varchar(100)
declare @id int
set @id = 7
select @result=Name,@id=Parent from temp where id=@id
while(exists(select 1 from temp where id=@id))
begin
    select @id=Parent,@result=case @result when '' then Name else Name + '-->' end + @result from temp where id=@id
end
select @result

Upvotes: 0

Mike Haboustak
Mike Haboustak

Reputation: 2296

You can implement recursion in Sql Server 2005+ using a common table expression. CTEs let you join to themselves in order to recurse. The CTE continues to recurse until no rows are returned, so you'll want to ensure you reliably meet that end condition.

declare @folders as table (id int, name nvarchar(20), parent int);
insert into @folders values(1, 'Root',  null);
insert into @folders values(2, 'Root_A', 1);
insert into @folders values(3, 'Root_B', 1);
insert into @folders values(4, 'Root_C', 1);
insert into @folders values(5, 'Root_C_A', 4);
insert into @folders values(6, 'Root_C_A_A', 5);
insert into @folders values(7, 'Root_C_A_A_A', 6);

declare @folderID int;
set @folderID=7;

with Folders (id, name, parent, number) as
(
    select ID, name, parent, 0 as number 
        from @folders 
        where id=@folderID
    union all
    select i.ID, i.Name, i.Parent, d.number + 1
        from @folders as i
        inner join Folders as d on d.Parent = i.ID
)
select id, name, number
from Folders
order by number desc;

Upvotes: 1

Related Questions