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