sqlserver2000中如何对部门进行递归查询

部门表的数据结构

/*==============================================================*/
/* Table: qxgl_dept */
/*==============================================================*/
create table qxgl_dept (
dept_id varchar(32) not null, --部门id
dept_name varchar(32) not null, --部门名称
dept_code varchar(32) null, --部门代码
dept_parent_id varchar(32) not null, --父部门
dept_level int not null, --部门级别
dept_id_path varchar(1024) not null, --部门id全路径
dept_fullname varchar(1024) not null, --部门全称
dept_order_id int not null default 0, --部门排序号
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

--首先在sqlserver2000中创建用户函数
--查询父单位的函数
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 --如果参数@Level小于“1”，则得到所有父单位
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

--查询子单位的函数
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 --如果参数@Level小于“1”，则得到所有子单位
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

--查询
select * from GetParentDept('402881601b553a1d011b553d31c2000b',0)
select * from GetChildDept('dept111122223333444455556666dept',1)

--删除函数
drop Function GetParentDept
drop Function GetChildDept