SQL Server 2000 recursive Operation Method

Unlike connect by in Oracle, SQL Server 2000 does not have the same usage method and needs to be written by itself.

 

There is an organizational unit table with two key fields: groupid and g_parentid. The former is its own ID, and the latter is the ID of the parent node. You need to read the tree structure through SQL, the details are as follows:

 

 

1. Create a table:

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [sys_group] ') and objectproperty (ID, n'isusertable') = 1)
Drop table [DBO]. [sys_group]
Go

Create Table [DBO]. [sys_group] (
[Groupid] [int] identity (1, 1) not null,
[G_cname] [nvarchar] (50) Collate chinese_prc_ci_as null,
[G_parentid] [int] not null,
[G_showorder] [int] not null,
[G_level] [int] Null,
[G_childcount] [int] Null,
[G_delete] [tinyint] Null
) On [primary]
Go

 

Ii. Create a UDF:

Create Function f_getgrouplist (@ groupid numeric)
Returns @ t_level table (groupid numeric, g_level INT)
As
Begin
Declare @ g_level int
Set @ g_level = 1
Insert @ t_level select @ groupid, @ g_level
While @ rowcount> 0
Begin
Set @ g_level = @ g_level + 1
Insert @ t_level select a. groupid, @ g_level
From sys_group A, @ t_level B
Where a. g_parentid = B. groupid
And B. g_level = @ g_Level-1
End
Return
End

 

Iii. Usage:

Select .*
From sys_group A, f_getgrouplist ('2') B
Where a. groupid = B. groupid

 

 

The preceding method can be used to read the tree structure under a specified node.