通过父部门ID查询所有子部门的存储过程

Question

if exists(select * from sysobjects where name='Proc_kq_GetSubDptId') drop proc Proc_kq_GetSubDptId
go
create procedure Proc_kq_GetSubDptId  --获取部门下包含子部门ID
(
  @sDeptID varchar(100), --总部门ID
  @sAllSubDeptID varchar(888) output --返回包含自身及子部门ID
)
as 
begin
  Declare 
          @cur_dptid cursor,
          @sTemp varchar(100),
          @sDeparts varchar(888),
          @sTmpSubDptid varchar(50),
          @isnull bit --类似布尔值？
  set @sAllSubDeptID = @sDeptID  --默认返回自身部门ID
  set @sDeparts =''''+@sDeptID+''''
  set @sTemp = @sAllSubDeptID   --条件变量
  set  @isnull=1 --默认顶级部门存在
  while @isnull=1 --当部门下还有子部门的时候就循环调用
  begin
    print @sTemp
    set @cur_dptid = cursor   for
       select Levelid from pb_depart where cast(Departid as varchar(100)) in(@sTemp) order by levelid--查出部门下的所有部门编号   
    set @isnull=0 --递归退出条件
    set @sTemp='' --父部门ID先清空
    open @cur_dptid  --第二次循环 怀疑游标在存在还存在
    --从游标中提取子部门ID
    fetch next from @cur_dptid into @sTmpSubDptid
    --当有记录
    while @@fetch_status=0 
    begin
     set @sTmpSubDptid=''''+@sTmpSubDptid+''''
     set @isnull=1 --存在子部门
     set @sTemp = @sTemp+','+@sTmpSubDptid  --select 条件改变,当前子部门变为父部门
     set @sDeparts = @sDeparts +','+ @sTmpSubDptid
     fetch next from @cur_dptid into @sTmpSubDptid  --取下一条记录
    end
    close   @cur_dptid  --关闭游标
    deallocate @cur_dptid -- 删除游标引用
    if not @sTemp=''  set @sTemp=Right(@sTemp,len(@sTemp)-1)  --删除第一个多出的句号
    set @sAllSubDeptID =@sDeparts
  end
  print @sAllSubDeptID
end返回的结果是
10
'1001','1002'
'10','1001','1002'本来应该返回
10
'1001','1002'
'10','1001','1002','100201'
因为部门ID1002下还有个子部门是100201
怀疑是@TEMP变量有问题，高手帮我看看啊

Answer 1

create   table   BOM(ID   INT,PID   INT)   
insert   into   BOM   select   1,0
insert   into   BOM   select   2,1 
insert   into   BOM   select   3,1 
insert   into   BOM   select   4,2   
insert   into   BOM   select   5,0  
insert   into   BOM   select   6,3
insert   into   BOM   select   7,6
go  
   
create proc Proc_kq_GetSubDptId
@sDeptID VARCHAR(100),
@sAllSubDeptID varchar(1000) output
as   
declare @t   table(ID   VARCHAR(10),PID   VARCHAR(10),Level   INT)    
declare @i int
set @i=1   
insert into @t select ID,PID,@i from BOM where id=@sDeptID   
while @@rowcount<>0   
begin   
set @i=@i+1   
insert into @t select a.ID,a.PID,@i from BOM a,@t b  where a.PID=b.ID and b.Level=@i-1   
end
select @sAllSubDeptID=isnull(@sAllSubDeptID+',','')+ltrim(id) from @t order by level     
go   
    --调用
declare @childids varchar(1000)
exec Proc_kq_GetSubDptId 1,@childids output 
select @childids as '自身加子节点'--结果：
自身加子节点
------------
1,2,3,4,6,7

Answer 2

  额，谢谢了。我还是想知道我那段代码到底哪里出错了呢。大致感觉@stemp这个变量第二次select 取出的数值时空的?因为我在存储过程调试的时候,输入参数 '10'，就没有返回子部门，参数10就可以返回下面两个子部门

Answer 3

http://blog.csdn.net/ws_hgo/archive/2010/01/31/5274571.aspx

Answer 4

create table #EnterPrise
(
  Department nvarchar(50),--部门名称
  ParentDept nvarchar(50),--上级部门
  DepartManage nvarchar(30)--部门经理
)
insert into #EnterPrise select '技术部','总经办','Tom'
insert into #EnterPrise select '商务部','总经办','Jeffry'
insert into #EnterPrise select '商务一部','商务部','ViVi'
insert into #EnterPrise select '商务二部','商务部','Peter'
insert into #EnterPrise select '程序组','技术部','GiGi'
insert into #EnterPrise select '设计组','技术部','yoyo'
insert into #EnterPrise select '专项组','程序组','Yue'
insert into #EnterPrise select '总经办','','Boss'
--查询部门经理是Tom的下面的部门名称
;with hgo as
(
   select *,0 as rank from #EnterPrise where DepartManage='Tom'
   union all
   select h.*,h1.rank+1 from #EnterPrise h join hgo h1 on h.ParentDept=h1.Department
)
select * from hgo
/*
Department           ParentDept                DepartManage      rank
--------------- -------------------- ----------------------- -----------
技术部               总经办                    Tom               0
程序组               技术部                    GiGi              1
设计组               技术部                    yoyo              1
专项组               程序组                    Yue               2
*/
--查询部门经理是GiGi的上级部门名称
;with hgo as
(
   select *,0 as rank from #EnterPrise where DepartManage='GiGi'
   union all
   select h.*,h1.rank+1 from #EnterPrise h join hgo h1 on h.Department=h1.ParentDept
)
select * from hgo
/*
Department               ParentDept          DepartManage    rank
-------------------- ----------------------  -----------  -----------
程序组                   技术部                 GiGi           0
技术部                   总经办                 Tom            1
总经办                                          Boss           2
*/本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/ws_hgo/archive/2010/01/31/5274571.aspx

Answer 5

晕，我自己的逻辑感觉没错的。就是结果错了
关键是这句select Levelid from pb_depart where cast(Departid as varchar(100)) in(@sTemp) order by levelid
第一次参数是 10 有返回结果 '1001','1002'然后 就把 '1001','1002' 这个赋值给@sTemp这个变量。查询出来 没有结果了 按理还有个 100201这个部门的