How to perform recursive query on a department in sqlserver2000

Department Table Data Structure

/* ===================================================== ======================================= */
/* Table: qxgl_dept */
/* ===================================================== ======================================= */
Create Table qxgl_dept (
Dept_id varchar (32) not null, -- department ID
Dept_name varchar (32) not null, -- department name
Dept_code varchar (32) null, -- department code
Dept_parent_id varchar (32) not null, -- parent department
Dept_level int not null, -- department level
Dept_id_path varchar (1024) not null, -- department ID full path
Dept_fullname varchar (1024) not null, -- department name
Dept_order_id int not null default 0, -- Department Order Number
Dept_area varchar (32) not null,
Dept_type varchar (32) not null default '0 ',
Dept_linkman varchar (32) null,
Dept_linkmanphone varchar (32) null,
Dept_email varchar (32) null,
Dept_phone varchar (32) null,
Dept_fax varchar (32) null,
Dept_address varchar (255) null,
Dept_postalcode varchar (32) null,
Dept_introduce varchar (2000) null,
Dept_create_time timestamp null default getdate (),
Constraint pk_qxgl_dept primary key (dept_id ),
Constraint ak_key_2_qxgl_dep unique (dept_name)
)
Go

 

-- Create a User Function in sqlserver2000
-- Query the function of the parent unit
Create Function getparentdept (@ dept_id varchar (32), @ level INT)
Returns @ parents table ([dept_id] varchar (32), [dept_name] varchar (128), [dept_parent_id] varchar (32 ))
As
Begin
If @ level <1 -- if the parameter @ level is less than "1", all parent units are obtained.
Begin
Insert @ parents
Select dept_id, dept_name, dept_parent_id
From [qxgl_dept]
Where [dept_id] = (select [dept_parent_id]
From [qxgl_dept] Where [dept_id] = @ dept_id)

While @ rowcount> 0
Begin
Insert @ parents
Select B. dept_id, B. dept_name, B. dept_parent_id
From @ parents a inner join [qxgl_dept] B
On a. [dept_parent_id] = B. [dept_id]
Where B. [dept_parent_id] Not in
(Select distinct [dept_parent_id] From @ parents)
End
End
Else
Begin
Set @ level = @ level-1

Insert @ parents
Select dept_id, dept_name, dept_parent_id
From [qxgl_dept]
Where [dept_id] = (select [dept_parent_id]
From [qxgl_dept] Where [dept_id] = @ dept_id)

While @ rowcount> 0 and @ level> 0
Begin
Set @ level = @ level-1
Insert @ parents
Select B. dept_id, B. dept_name, B. dept_parent_id
From @ parents a inner join [qxgl_dept] B
On a. [dept_parent_id] = B. [dept_id]
Where B. [dept_parent_id] Not in
(Select distinct [dept_parent_id] From @ parents)
End
End
Return
End

-- Query Sub-Unit functions
Create Function getchilddept (@ dept_id varchar (32), @ level INT)
Returns @ child table ([dept_id] varchar (32), [dept_name] varchar (64), [dept_parent_id] varchar (32 ))
As
Begin
If @ level <1 -- if the parameter @ level is less than "1", all sub-units are obtained.
Begin
Insert @ child
Select dept_id, dept_name, dept_parent_id
From [qxgl_dept] Where [dept_parent_id] = @ dept_id
While @ rowcount> 0
Begin
Insert @ child
Select B. dept_id, B. dept_name, B. dept_parent_id
From @ child a inner join [qxgl_dept] B
On a. [dept_id] = B. [dept_parent_id]
Where B. [dept_parent_id] Not in
(Select distinct [dept_parent_id] From @ child)
End
End
Else
Begin
Set @ level = @ level-1
Insert @ child
Select dept_id, dept_name, dept_parent_id
From [qxgl_dept] Where [dept_parent_id] = @ dept_id
While @ rowcount> 0 and @ level> 0
Begin
Set @ level = @ level-1
Insert @ child
Select B. dept_id, B. dept_name, B. dept_parent_id
From @ child a inner join [qxgl_dept] B
On a. [dept_id] = B. [dept_parent_id]
Where B. [dept_parent_id] Not in
(Select distinct [dept_parent_id] From @ child)
End
End
Return
End

-- Query
Select * From getparentdept ('402881601b553a1d011b553d31c2000b', 0)
Select * From getchilddept ('dep111122223333444455556666dept', 1)

-- Delete a function
Drop function getparentdept
Drop function getchilddept