Get a tree from self-recursion in the table (that is, ID and parentid)

Let's look at two instances first.

Create FUNCTION [f_Get_DownCorpTree] (@ CorpID int)
Returns @ CorpTree TABLE
(
CorpID int
)
As
Begin
-- Call method: Select * From f_Get_DownCorpTree (20)

-- Get the company's next-level company set
-- SET NOCOUNT ON

-- Create table [Org_Corp] (
-- [ID] [int] IDENTITY (1, 1) not null,
-- [ParentID] [int] NULL,
-- [CorpCode] [nvarchar] (255) not null,
-- [CorpName] [nvarchar] (255) NULL
-- CONSTRAINT [Org_Corp_PK] PRIMARY KEY CLUSTERED
--(
-- [ID]
--) ON [PRIMARY]
--) ON [PRIMARY]
-- GO

DECLARE @ Cnt int
Declare @ I int
Declare @ tmpnode int

Declare @ stack Table (node int)
Declare @ stackTmp Table (node int)
Declare @ stackTmpXXX Table (node int)

Insert into @ stack
Select [ID] from vRef_Org_Corp Where ParentID = @ CorpID

Insert into @ stackTmp
Select [ID] from vRef_Org_Corp Where ParentID = @ CorpID

Select @ tmpnode = @ CorpID
Select @ Cnt = count (*) from vRef_Org_Corp
Select @ I = 0

Loops:

Declare cur cursor for select node from @ stackTmp
Open cur
Fetch next from cur into @ tmpnode
While @ FETCH_STATUS = 0
Begin
Insert into @ stack select [ID] from vRef_Org_Corp Where ParentID = @ tmpnode
Insert into @ stacktmpxxx select [ID] From vref_org_corp where parentid = @ tmpnode
Select @ I = @ I + 1
Fetch next from cur into @ tmpnode
End
Close cur
Deallocate cur

Select @ I = @ I + 1

Delete from @ stacktmp
Insert into @ stacktmp select node from @ stacktmpxxx
Delete from @ stacktmpxxx

If (@ I >=@ CNT * 1.5)
Goto ends

Goto Loops

Ends:

Insert into @ corptree (corpid)
Select node from @ Stack

-- Select * from [org_corp]
-- Where exists
--(
-- Select 1 from @ corptree where corpid = [org_corp]. [ID]
--)
Return

End

-------------------

Create FUNCTION [f_Get_UpCorpTree] (@ CorpID int)
Returns @ CorpTree TABLE
(
CorpID int
)
As
Begin
-- Call method: Select * From f_Get_UpCorpTree (8)

-- Select * from Org_Corp
-- Declare @ CorpID int
-- Select @ CorpID = 9
-- Declare @ CorpTree Table (CorpID int, flag varchar (10 ))

-- Create table [Org_Corp] (
-- [ID] [int] IDENTITY (1, 1) not null,
-- [ParentID] [int] NULL,
-- [CorpCode] [nvarchar] (255) not null,
-- [CorpName] [nvarchar] (255) NULL
-- CONSTRAINT [Org_Corp_PK] PRIMARY KEY CLUSTERED
--(
-- [ID]
--) ON [PRIMARY]
--) ON [PRIMARY]
-- GO

 

Declare @ TmpCorpID int
Select @ TmpCorpID = @ CorpID

-- Get the company's top-level company
GetUpCorp:
Select @ TmpCorpID = [ParentID] From [vRef_Org_Corp] Where [ID] = @ TmpCorpID
If (@ TmpCorpID is not null)
Begin
-- Print Convert (varchar (10), @ TmpCorpID)
Insert into @ CorpTree (CorpID) Values (@ TmpCorpID)
Goto GetUpCorp
End
Return
End

The stored procedure for generating such results is as follows:

Create Procedure [dbo]. [pCreateFunction_DownTree] (@ Table_Obj nvarchar (200 ))
As
Begin

Declare @ SQL nvarchar (2000)
Select @ SQL ='
Create FUNCTION f_Get_Down <% Table_Obj %> Tree (@ ObjID int)
Returns @ <% Table_Obj %> Tree TABLE
(
[ObjID] int not null
)
As
Begin
-- Call method: Select * From f_Get_Down <% Table_Obj %> Tree (20)
/*
Select *
From <% Table_Obj %>
Where ID in
(
Select ObjID From f_Get_Down <% Table_Obj %> Tree (1)
)
*/

DECLARE @ Cnt int
Declare @ I int
Declare @ tmpnode int

Declare @ stack Table (node int)
Declare @ stackTmp Table (node int)
Declare @ stackTmpXXX Table (node int)

Insert into @ stack
Select [ID] from <% Table_Obj %> Where ParentID = @ ObjID

Insert into @ stackTmp
Select [ID] from <% Table_Obj %> Where ParentID = @ ObjID

Select @ tmpnode = @ ObjID
Select @ Cnt = count (*) from <% Table_Obj %>
Select @ I = 0

Loops:

Declare cur cursor for select node from @ stackTmp
Open cur
Fetch next from cur into @ tmpnode
While @ FETCH_STATUS = 0
Begin
Insert into @ stack select [ID] from <% Table_Obj %> Where ParentID = @ tmpnode
Insert into @ stackTmpXXX select [ID] from <% Table_Obj %> Where ParentID = @ tmpnode
Select @ I = @ I + 1
Fetch next from cur into @ tmpnode
End
CLOSE cur
DEALLOCATE cur

Select @ I = @ I + 1

Delete from @ stackTmp
Insert into @ stackTmp select node from @ stackTmpXXX
Delete from @ stacktmpxxx

If (@ I >=@ CNT * 1.5)
Goto ends

Goto Loops

Ends:
Insert into @ stack (node) Select @ objid

Insert into @ <% table_obj %> tree ([objid])
Select node from @ Stack

Return

End

'

Select @ SQL = Replace (@ SQL, '<% table_obj %>', @ table_obj)
Print @ SQL

Execute (@ SQL)

End

---------------

Create procedure [DBO]. [pcreatefunction_uptree] (@ table_obj nvarchar (200 ))
As
Begin

Declare @ SQL nvarchar (2000)
Select @ SQL ='
Create Function [f_get_up <% table_obj %> tree] (@ objid INT)
Returns @ <% table_obj %> tree table
(
ObjID int
)
As
Begin
-- Call method: Select ObjID From f_Get_Up <% Table_Obj %> Tree (8)

Declare @ TmpObjID int
Select @ TmpObjID = @ ObjID

-- Obtain the Obj of the previous Obj level.
GetUpObj:
Select @ TmpObjID = [ParentID] From [<% Table_Obj %>] Where [ID] = @ TmpObjID
If (@ TmpObjID is not null and @ TmpObjID <> @ ObjID)
Begin
-- Print Convert (varchar (10), @ TmpObjID)
Insert into @ <% Table_Obj %> Tree (ObjID) Values (@ TmpObjID)
Goto GetUpObj
End
Return
End
'

Select @ SQL = Replace (@ SQL, '<% Table_Obj %>', @ Table_Obj)
Print @ SQL

Execute (@ SQL)

End

:_)