database table Looping

There are two fields in a table: Orgid,parentorgid. OrgID is the primary key, Parentorgid is the parent unit OrgID. The requirements came, which now need an infinite recursion based on any one of the orgid values of the current table to isolate it subordinate to all orgid.

BEGIN
DECLARE @ParentOrgID VARCHAR (32); --Parent Organization ID
CREATE Table #TempBase_Org--Temporary main table
(
OrgID VARCHAR (32)
)
Insert #TempBase_Org Select OrgID from dbo. base_org where [email protected] --Identify the current child section ID according to the parent Parentorgid and insert the temporary table.
While @ @rowcount <>0-This is the most critical, loop-recursive call.
BEGIN
Insert #TempBase_Org Select A.orgid from base_org a
INNER JOIN #TempBase_Org B on A.parentorgid=b.orgid and
Not EXISTS (select 1 from #TempBase_Org where Orgid=a.orgid)
END
INSERT into #TempBase_Org VALUES (@ParentOrgID); --Insert the ancestor ID into the temporary table.
END

The above is the main SQL statement.

database table Looping