谢谢!! CREATE FUNCTION dbo.GetAllNotAnalyzeDeptUser (@InputDeptID int , @Pass int , @DeptUsersID varchar(5000)) RETURNS varchar (5000) AS BEGIN Declare @SingleUserID varchar(100) Declare @SingleUserName varchar(100) Declare @CycleID int Declare @Ret varchar(4000)set @DeptUsersID=@DeptUsersID+'&&&'+convert(varchar,@InputDeptID) if @Pass=1 begin ----------------------------------------------------------- declare datecur0 CURSOR FOR select UserID,UserName from users where DeptID=@InputDeptID open datecur0 fetch next from datecur0 into @SingleUserID,@SingleUserName while @@fetch_status=0 begin set @DeptUsersID=@DeptUsersID+','+@SingleUserName+' <'+@SingleUserID+'>' fetch next from datecur0 into @SingleUserID,@SingleUserName end close datecur0 deallocate datecur0 ------------------------------------------------------------- endif (select count(*) from Department where ParentID=@InputDeptID)>0 begin declare datecur CURSOR FOR select id from Department where ParentID=@InputDeptID open datecur fetch next from datecur into @CycleID while @@fetch_status=0 begin ----------------------------------------------------------- declare getdeptuser CURSOR FOR select UserID,UserName from users where DeptID=@CycleID open getdeptuser fetch next from getdeptuser into @SingleUserID,@SingleUserName while @@fetch_status=0 begin set @DeptUsersID=@DeptUsersID+','+@SingleUserName+' <'+@SingleUserID+'>' select @Ret=dbo.GetAllNotAnalyzeDeptUser (@CycleID,'0',@DeptUsersID) -----------主要是这里!!!!!!! fetch next from getdeptuser into @SingleUserID,@SingleUserName end close getdeptuser deallocate getdeptuser -------------------------------------------------------------- fetch next from datecur into @CycleID end close datecur deallocate datecur end Return @DeptUsersID END
CREATE FUNCTION dbo.GetAllNotAnalyzeDeptUser (@InputDeptID int , @Pass int , @DeptUsersID varchar(5000))
RETURNS varchar (5000) AS
BEGIN Declare @SingleUserID varchar(100)
Declare @SingleUserName varchar(100)
Declare @CycleID int
Declare @Ret varchar(4000)set @DeptUsersID=@DeptUsersID+'&&&'+convert(varchar,@InputDeptID)
if @Pass=1
begin
-----------------------------------------------------------
declare datecur0 CURSOR FOR
select UserID,UserName from users where DeptID=@InputDeptID
open datecur0
fetch next from datecur0 into @SingleUserID,@SingleUserName
while @@fetch_status=0
begin
set @DeptUsersID=@DeptUsersID+','+@SingleUserName+' <'+@SingleUserID+'>'
fetch next from datecur0 into @SingleUserID,@SingleUserName
end
close datecur0
deallocate datecur0
-------------------------------------------------------------
endif (select count(*) from Department where ParentID=@InputDeptID)>0
begin
declare datecur CURSOR FOR
select id from Department where ParentID=@InputDeptID
open datecur
fetch next from datecur into @CycleID
while @@fetch_status=0
begin
-----------------------------------------------------------
declare getdeptuser CURSOR FOR
select UserID,UserName from users where DeptID=@CycleID
open getdeptuser
fetch next from getdeptuser into @SingleUserID,@SingleUserName
while @@fetch_status=0
begin
set @DeptUsersID=@DeptUsersID+','+@SingleUserName+' <'+@SingleUserID+'>'
select @Ret=dbo.GetAllNotAnalyzeDeptUser (@CycleID,'0',@DeptUsersID) -----------主要是这里!!!!!!!
fetch next from getdeptuser into @SingleUserID,@SingleUserName
end
close getdeptuser
deallocate getdeptuser
--------------------------------------------------------------
fetch next from datecur into @CycleID
end
close datecur
deallocate datecur
end Return @DeptUsersID END
我想可能与游标的嵌套有关吧。
你要的结果就是列出某部门及下属所有部门的人员名单,建议你在department这个表里加一个字段,这个字段不需要公开,只做为内部使用,比如叫interId,值是这样的:
deptid parentid interId
1 null 1
2 null 2
3 1 1.3
4 1 1.4
5 3 1.3.5
6 5 1.3.5.6
这样类似的问题非常好解决。比如对deptid=1的所有下级部分,只需用interId like '1%'即可搞定。试下吧,反正我做过类似的分级统计,就是这样做的。