CREATE TABLE [dbo].[department](  
 [ID] [decimal](18, 0) IDENTITY(1,1) NOT NULL,  
 [department] [nvarchar](20) NULL,  
 [pid] [decimal](18, 0) NULL   
)  

CREATE PROCEDURE [dbo].[getChildDeptById]   
 @id INT  
AS  
BEGIN  
 -- SET NOCOUNT ON added to prevent extra result sets from  
 -- interfering with SELECT statements.  
 -- SET NOCOUNT ON;  
  
 WITH   dept  
          AS ( SELECT   *  
               FROM     dbo.department  
               WHERE    pid = @id  
               UNION ALL  
               SELECT   d.*  
               FROM     dbo.department d  
                        INNER JOIN dept ON d.pid = dept.id  
             )  
    SELECT  *  
    FROM    dept  
END  

CREATE PROCEDURE [dbo].[getParentDeptById]   
 @id INT  
AS  
BEGIN  
 -- SET NOCOUNT ON added to prevent extra result sets from  
 -- interfering with SELECT statements.  
 -- SET NOCOUNT ON;   
 WITH   dept  
          AS ( SELECT   dp.*  
               FROM     dbo.department d INNER JOIN dbo.department dp ON d.pid=dp.ID  
               WHERE    d.id = @id  
               UNION ALL  
               SELECT   d.*  
               FROM     dbo.department d  
                        INNER JOIN dept ON d.id = dept.pid  
             )  
    SELECT  *  
    FROM    dept   
END

USE [ZLDC_CostControl]
GO
/****** Object:  StoredProcedure [dbo].[SP_GetParentOrganizationUnitByOrgUnitGUID]    Script Date: 2017/7/1 星期六 15:55:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <yk>
-- Create date: 2017年7月1日15:36:35
-- Description:    根据组织的GUID获取其所有的上级组织的名称
--使用场景:根据登录用户获取其所属机构的名称 (地产集团/集团总部 运营内控中心/信息管理)
-- =============================================
ALTER PROCEDURE [dbo].[SP_GetParentOrganizationUnitByOrgUnitGUID]   
 @OrgUnitGUID Nvarchar(50)  
AS  
BEGIN  
 -- SET NOCOUNT ON added to prevent extra result sets from  
 -- interfering with SELECT statements.  
 -- SET NOCOUNT ON;   
 WITH   dept  
          AS (   
              SELECT   dp.OrgUnitName ,d.OrgUnitGUID,d.UpperOrgUnitGUID
               FROM     dbo.TB_OrganizationUnit d INNER JOIN dbo.TB_OrganizationUnit dp ON d.UpperOrgUnitGUID=dp.OrgUnitGUID  
               WHERE    d.OrgUnitGUID =@OrgUnitGUID   
               UNION ALL  
               SELECT   d.OrgUnitName ,d.OrgUnitGUID,d.UpperOrgUnitGUID
               FROM     dbo.TB_OrganizationUnit d  
                        INNER JOIN dept ON d.OrgUnitGUID = dept.UpperOrgUnitGUID 
             )  
    SELECT  *  
    FROM    dept   
END 

 //经办人部门的设置
            //获取所有上级部门 通过存储过程
            DataSet deptmentDS = TBContractSearch.GetParentOrganizationUnitByOrgUnitGUID(CurrentUser.OrganizationId);
            if (deptmentDS != null && deptmentDS.Tables[0].Rows.Count>0)
            {
                int count = deptmentDS.Tables[0].Rows.Count;
                StringBuilder deptNameSB = new StringBuilder(); 
                for (int i = count-1; i > 0; i--)
                {
                    deptNameSB.Append(deptmentDS.Tables[0].Rows[i]["OrgUnitName"]);
                    deptNameSB.Append(‘/‘);
                }
                string deptName = deptNameSB.ToString().TrimEnd(‘/‘);
                this.attnDepart.Text = deptName;