create function f_GetChildId(@ID int)
returns @tblReturn table(ParentID varchar(255),ID int,OrgName varchar(255))
AS
Begin
insert into @tblReturn select ParentID,ID,OrgName from org where ParentID=@ID
while @@rowcount>0
insert into @tblReturn select A.ParentID,A.ID,A.OrgName
from org A inner join @tblReturn B on A.ParentID=B.ID
where A.ID not in(select ID from @tblReturn)
return
end
returns @tblReturn table(ParentID varchar(255),ID int,OrgName varchar(255))
AS
Begin
insert into @tblReturn select ParentID,ID,OrgName from org where ParentID=@ID
while @@rowcount>0
insert into @tblReturn select A.ParentID,A.ID,A.OrgName
from org A inner join @tblReturn B on A.ParentID=B.ID
where A.ID not in(select ID from @tblReturn)
return
end
returns @tblReturn table(ParentID varchar(255),ID int,OrgName varchar(255))--返回值是一个表@tblReturnAS
Begin
insert into @tblReturn select ParentID,ID,OrgName from org where ParentID=@ID
while @@rowcount> 0 --查询出所有ParentID为@ID(函数调用时输入的参数)的值写入 @tblReturn表中,其实也就是@ID的直接子节点 insert into @tblReturn select A.ParentID,A.ID,A.OrgName
from org A inner join @tblReturn B on A.ParentID=B.ID
where A.ID not in(select ID from @tblReturn) --查询出所有ParentID为@tblReturn表中id值的子节点(函数调用时输入的参数)的值写入 @tblReturn表中,其实也就是@ID的二级子节点 return
end这个函数并不是找出节点下的所有子节点,而是找出节点下所有的直接子节点和二级子节点,然后放入到@tblReturn表中返回。