CTE WIth递归树形查找

SQL脚本，相比以往程序自己调用自己，我们可以通过SQL语句，cte with 模块语句，SQL语句模块调用子级查询

/*
根据子节点节点递归父级所有根节点
2019-01-24
**/
CREATE proc [dbo].[getparentidbychildid]
@rootchildid varchar(500)--子级节点ID
as
begin
declare @childid varchar(500)
set @childid=@rootchildid
declare @cetparentid varchar(500)
select @cetparentid=ParentId from [org_userorg] where UserOrgId=@childid;
with ctegetparent as
(
select * from [org_userorg] where UserOrgId=@cetparentid
union all
(select a.* from [org_userorg] as a inner join
ctegetparent as b on a.UserOrgId=b.ParentId)
)
select OrgID,UserOrgId,UserOrgName,UserOrgFullName,ParentId from ctegetparent
end
GO