递归生成longNumber

update dbo.sys_company set longNumber='' where ID=207

Declare @Id nvarchar(36)
Set @Id = '207'; ---在此修改父节点

With RootNodeCTEee(ID,parentID,longnumber)
As
(
Select ID,parentID,longnumber From sys_company Where ID In (@Id)
Union All
Select sys_company.ID,sys_company.parentID,sys_company.longnumber From RootNodeCTEee
Inner Join sys_company
On RootNodeCTEee.ID = sys_company.parentID
)
select * into #T from RootNodeCTEee
--select * from #T

declare @IID varchar(36) --
declare @parentID varchar(36) --
declare @longnumber varchar(512) --
declare @Templongnumber varchar(512) --
Declare row Cursor For --声明游标row
Select ID,parentID,longnumber From #T--
--select * from RootNodeCTEee

Open row
Fetch Next From row into @IID,@parentID,@longnumber --
Fetch Next From row into @IID,@parentID,@longnumber --
While @@FETCH_STATUS = 0 --完成状态
begin
print(@IID+'__'+@parentID+'__'+@longnumber)
select @Templongnumber= longnumber + '.'+convert(varchar,ID) from sys_company where ID=@parentID
update sys_company set longnumber = @Templongnumber where ID = @IID

Fetch Next From row into @IID,@parentID,@longnumber

end
Close row
Deallocate row

drop table #T