----------查找所有子孩子,返回所有子孩子的记录表
--------@ParentD,查找ID是@ParentD的所有子孩子
CREATE PROCEDURE SubChilds
@ParentD int
AS
set nocount on
declare @temTable table(LeaguerID int not null, --记录ID
level1 int null default 0, --所在的层(父节点所在层为0,依次类推)
Location varchar(1) null, --所在区(分左区A,右区B)
UserName varchar(50) null --用户名字
)
declare @l int
set @l = 0
declare @LeaguerID int
declare @Location varchar(1)
declare @UserName varchar(50)
--插入父节点
select @LeaguerID=LeaguerID,@Location=rtrim(Location),@UserName=UserName from Rw_User where LeaguerID = @ParentD
insert into @temTable(LeaguerID,level1,Location,UserName) values(@LeaguerID,@l,@Location,@UserName)
--在插入成功的条件下,一层一层循环插入所有的子孩子,直到最后一层
while @@rowcount>0
begin
set @l = @l + 1
insert into @temTable select a.LeaguerID,level1 = @l,a.Location,a.UserName from Rw_User a join @temTable b on a.SuperiorID=b.LeaguerID
where b.level1=@l-1
end
--返回结果
select LeaguerID,level1,Location,UserName from @temTable where level1 <> 0
if @LeaguerID > 0
return 1
else
return 0
set nocount off
GO