set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Alter proc [dbo].[TreeView]
as
declare @t table
(
[Virtual_Field] [varchar](80),
[Virtual_Parent] [varchar](80),
[RM_Id] [int],
[RM_Value] [varchar](50),
[RM_Comment] [varchar](50),
[PM_Status] [varchar](50),
[RM_LastUpdate] [varchar](50),
[RM_Desc] [varchar](500),
[Project_Id] [int],
[Status_Id] [int],
[Sort] [int],
[Parent_RM_Id] [int],
[Depth] [int]
)
insert @t
select cast(a.sort as varchar),cast(a.sort as varchar),* from D_Requirement a
where parent_rm_id=-1
declare @level int
set @level=1
while @level<1000
begin
set @level=@level+1
if exists(
select * from D_Requirement
where Parent_RM_Id in (select RM_Id from @t)
and RM_Id not in (select RM_Id from @t)
)
begin
insert @t
select
(select [Virtual_Field] from @t where RM_Id =a.Parent_RM_Id)+'.'+cast(a.sort as varchar)
,(select [Virtual_Field] from @t where RM_Id =a.Parent_RM_Id)
,* from D_Requirement a
where Parent_RM_Id in (select RM_Id from @t)
and RM_Id not in (select RM_Id from @t)
end
else
begin
set @level=1001
end
end
select * from @t order by [Virtual_Field]
--exec treeview