East
 | Atlanta
 | | Legal
 | | | IS
 | | | Development
 | | Brokers
 | | | IS
 | Boston
 | | Admin.
 | | | Development

TABLE: tblHierElement
HierID HierDescr   HierLevel
------ ---------   ---------
1      East        0
2      Atlanta     1
3      Boston      1
4      Legal       2
5      Broker      2
6      Admin.      2
7      IS          3
8      Development 3

TABLE: tblHierRelation
relID rel_hierID parent_relID
----- ---------- ------------
1     1          0
2     2          1
3     4          2
4     7          3
5     8          3
6     5          2
7     7          6
8     3          1
9     6          8
10    8          9

create proc ap_getDescendants @startRelID int = 0
as

set nocount on

declare @nLevel int,
	@current_relID int

create table #stack (nLevel int, relID int)
create table #hierarchy (relID int, parent_relID int, hierID int, hasChild bit)

if @startRelID = 0
	begin
		select top 1 @nLevel = 1, @current_relID = relID from tblHierRelation where parent_relID = 0

		insert into #stack (nLevel, relID)
		select @nLevel, relID from tblHierRelation where parent_relID = 0
	end
else
	begin
		select top 1 @nLevel = 1, @current_relID = relID from tblHierRelation where relID = @startRelID
		insert into #stack (nLevel, relID)
		values (@nLevel, @current_relID)
	end

while (@nLevel > 0)
	begin
		if exists (select 'true' from #stack where nLevel = @nLevel)
			begin
				-- pop one record
				select top 1 @current_relID = relID from #stack where nLevel = @nLevel
				delete from #stack where relID = @current_relID and nLevel = @nLevel
				-- pop one record
				
				-- insert the record into hierarchy table
				insert into #hierarchy (relID, parent_relID, hierID, hasChild)
				select @current_relID, hr.parent_relID, hr.rel_hierID, case 
				when exists (select 'true' from tblHierRelation childHR where childHR.parent_relID = @current_relID) then 1
				else 0
				end
				from tblHierRelation hr where hr.relID = @current_relID

				-- insert leaf records (ones with no children) directly into #hierarchy table
				insert into #hierarchy (relID, parent_relID, hierID, hasChild)
				select hr.relID, hr.parent_relID, hr.rel_hierID, 0
				from tblHierRelation hr left outer join tblHierRelation hrB on hr.relID = hrB.parent_relID
				where hr.parent_relID = @current_relID and hrB.relID IS NULL

				-- insert non-leaf (ones that have some children) into #stack table
				insert into #stack (nLevel, relID)
				select @nLevel + 1, hr.relID
				from tblHierRelation hr inner join tblHierRelation hrB on hr.relID = hrB.parent_relID
				where hr.parent_relID = @current_relID

				-- if any leaf(s) found increment the @nLevel
				if @@rowcount > 0
					begin
						select @nLevel = @nLevel + 1
					end
			end
		else	-- if exists (select 'true' from #stack where nLevel = @nLevel)
			begin
				select @nLevel = @nLevel - 1
			end
	end -- while (@nLevel > 0)


select relID, parent_relID, hierID, hasChild
from #hierarchy

drop table #hierarchy

quote:

---

The first thing to do is to populate the parent nodes, which is unecessary if you use a single table, but it's easy to do in any case:

---

Select * From tree A
Where not exists(Select * From tree B Where parentNode = A.node)

quote:

---

I do not want to use Lineage column coz I need 24452 characters long string to save lineage of bottom most node in the hierarchy and this hierarchy is growing every day.

---