Window 2 World

This MS SQL SERVER procedure is used to get the list of all children , childrens children so on... for a given parent

CREATE PROCEDURE TEST
@id numeric(10)
AS
begin
create table #temp(parentid numeric(10) , level int,process bit,root numeric(10))
-- 0 level
insert into #temp values(@id,0,0,0)
declare @level int
set @level=0
select top 1 @id = parentid from #temp where level = @level and process=0;

while(@id is not null)
begin
update #temp set process =1 where parentid = @id and level = @level and process = 0;
insert into #temp select GRP_ID_N,@level+1,0,@id from USR_GRPS where parent_grp_id_n = @id
select top 1 @id = parentid from #temp where level = @level and process=0;
if @@rowcount = 0
begin
set @id = null
set @level = @level + 1
select top 1 @id = parentid from #temp where level = @level and process=0;
end
end

select * from #temp

end