Sample stored procedure for converting finite-layer tree data to infinite-layer tree data (ms SQL 2 K)

-- Example stored procedure for converting finite-layer tree data into infinite-layer tree data (ms SQL 2 K)
If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [p_tree_convert] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [p_tree_convert]
Go

Set quoted_identifier on
Go
Set ansi_nulls off
Go

 

Create procedure p_tree_convert

-- Example stored procedure for converting finite-layer tree data to infinite-layer tree data

-- Result data.
Create Table [DBO]. [# tree_info] (
[Nodeid] [varchar] (10) null, -- tree Data Node Code
[Parentid] [varchar] (10) null, -- parent node code of the tree data node
[Nodename] [nvarchar] (255) Collate chinese_prc_ci_as null, -- node name
[Address] [nvarchar] (255) Collate chinese_prc_ci_as null, -- other node Information
[Icon] [int] Null -- node icon number
) On [primary]

-- Get the data of a finite-layer tree data node
Select * #
From (select dpcode1 as node, dpname1 as nodename
From ora_custdept
Where (dpcode2 = '00') and (dpcode3 = '000') and (dpcode4 = '000 ')
Union all
Select dpcode1 + dpcode2 as node, dpname2 as nodename
From ora_custdept
Where (dpcode3 = '000') and (dpcode4 = '000') and (dpcode2 <> '00 ')
Union all
Select dpcode1 + dpcode2 + dpcode3 as node,
Dpname3 as nodename
From ora_custdept
Where (dpcode3 <> '000') and (dpcode4 = '000') and (dpcode2 <> '00 ')
Union all
Select dpcode1 + dpcode2 + dpcode3 + dpcode4 as node,
Dpname4 as nodename
From ora_custdept
Where (dpcode3 <> '000') and (dpcode4 <> '000') and (dpcode2 <> '00 '))
Tree_node
Order by Node

Insert into # tree_info (nodeid, parentid, nodename)
-- Obtain the data of the parent node from the finite-layer tree data node and insert the result table.
Select node, case when Len (ltrim (rtrim (node) = 2 then '0' when Len (node)
= 4 then substring (ltrim (rtrim (node), 1, 2) When Len (node)
= 7 then substring (ltrim (rtrim (node), 1, 4) When Len (node)
= 10 then substring (ltrim (rtrim (node), 1, 7) end as parentid, nodename
From #

Select * from # tree_info -- view the result
 
Drop table #
Drop table # tree_info

Go
Set quoted_identifier off
Go
Set ansi_nulls on
Go