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