create procedure f_GetChildId(@ID int)
AS
Begin
declare @tblReturn table(ParentID varchar(255),ID int,OrgName varchar(255))
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)
select * from @tblReturn
end
go
AS
Begin
declare @tblReturn table(ParentID varchar(255),ID int,OrgName varchar(255))
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)
select * from @tblReturn
end
go
(
@ID int
)
AS
select * from dbo.f_GetChildId(@ID)