create table hier
(
ID int identity(1,1),
PARENTID int null,
ISFOLDER int not null
)
go
insert into hier (parentid,isfolder) values(NULL,1)
insert into hier (parentid,isfolder) values(1,1)
insert into hier (parentid,isfolder) values(1,0)
insert into hier (parentid,isfolder) values(1,1)
insert into hier (parentid,isfolder) values(2,1)
insert into hier (parentid,isfolder) values(2,0)
insert into hier (parentid,isfolder) values(4,1)
insert into hier (parentid,isfolder) values(4,0)
insert into hier (parentid,isfolder) values(4,0)
insert into hier (parentid,isfolder) values(5,1)
insert into hier (parentid,isfolder) values(10,0)
insert into hier (parentid,isfolder) values(10,0)
go
create procedure ShowElementsUnderID
@id int
As
create table #tmp (id int not null,level int not null)
DECLARE @level int
Set @level=1
insert into #tmp Select id,@level from hier where id=@id
while @@rowcount>0
BEGIN
Set @level=@level+1
Insert Into
#tmp
Select
hier.id,
@level
from
hier
inner join #tmp
on hier.parentid=#tmp.id and #tmp.level=@level-1 and hier.IsFolder=1
END
Select
hier.id
From
hier
inner join #tmp
on #tmp.id=hier.parentid and hier.IsFolder=0
go
exec ShowElementsUnderID 1
exec ShowElementsUnderID 10
go
drop procedure ShowElementsUnderID
drop table hier
go |