初始化机构path

IF OBJECT_ID(‘tempdb..#testOU‘) IS NOT NULL DROP TABLE #testOU;
WITH oulist as
(
select CAST(‘0001‘ AS VARCHAR(max)) AS codes,a.* from dbo.Org a WHERE a.PlatformOrgId = ‘00000000000000000000000000000001‘ 
    union all
select 
w.codes
+  cast( ROW_NUMBER() over (order by a.sort) /1000 as  varchar(max)) 
+  cast( ROW_NUMBER() over (order by a.sort) % 1000 /100 as  varchar(max))
+  cast( ROW_NUMBER() over (order by a.sort) %1000 % 100 /10 as  varchar(max)) 
+  cast( ROW_NUMBER() over (order by a.sort) %1000 % 100 %10 as  varchar(max)) 

codes,
 a.* from  dbo.Org a 
    join oulist w on a.ParentID = w.PlatformOrgId 
)
SELECT *,len(Codes)/4-1 AS oLevel into #testOU FROM oulist order by code

update dbo.Org SET Org.OrgPath = b.codes from   dbo.Org o inner join #testOU  b on o.PlatformOrgId = b.PlatformOrgId 

IF OBJECT_ID(‘tempdb..#testOU‘) IS NOT NULL DROP TABLE #testOU;