CREATE PROCEDURE rb_GetTabsFlat ( @PortalID int ) AS --Create a Temporary table to hold the tabs for this query CREATE TABLE #TabTree ( [TabID] [int], [TabName] [nvarchar] (100), [ParentTabID] [int], [TabOrder] [int], [NestLevel] [int], [TreeOrder] [varchar] (1000) ) SET NOCOUNT ON -- Turn off echo of "... row(s) affected" DECLARE @LastLevel smallint SET @LastLevel = 0 -- First, the parent levels INSERT INTO #TabTree SELECT TabID, TabName, ParentTabID, TabOrder, 0, cast(100000000 + taborder as varchar) FROM rb_Tabs WHERE ParentTabID IS NULL and PortalID =@PortalID ORDER BY TabOrder -- Next, the children levels WHILE (@@rowcount > 0) BEGIN SET @LastLevel = @LastLevel + 1 INSERT #TabTree (TabID, TabName, ParentTabID, TabOrder, NestLevel, TreeOrder) SELECT rb_Tabs.TabID, Replicate('-', @LastLevel *2) + rb_Tabs.TabName, rb_Tabs.ParentTabID, rb_Tabs.TabOrder, @LastLevel, cast(#TabTree.TreeOrder as varchar) + '.' + cast(100000000 + rb_Tabs.TabOrder as varchar) FROM rb_Tabs join #TabTree on rb_Tabs.ParentTabID= #TabTree.TabID WHERE EXISTS (SELECT 'X' FROM #TabTree WHERE TabID = rb_Tabs.ParentTabID AND NestLevel = @LastLevel - 1) and PortalID =@PortalID ORDER BY #TabTree.TabOrder END --Get the Orphans INSERT #TabTree (TabID, TabName, ParentTabID, TabOrder, NestLevel, TreeOrder) SELECT rb_Tabs.TabID, '(Orphan)' + rb_Tabs.TabName, rb_Tabs.ParentTabID, rb_Tabs.TabOrder, 999999999, '999999999' FROM rb_Tabs WHERE NOT EXISTS (SELECT 'X' FROM #TabTree WHERE TabID = rb_Tabs.TabID) and PortalID =@PortalID -- Reorder the tabs by using a 2nd Temp table and an identity field to keep them straight. select IDENTITY(int,1,2) as ord , cast(tabID as varchar) as tabID into #tabs from #TabTree order by nestlevel, Treeorder -- Change the taborder in the sirt temp table so that tabs are ordered in sequence update #TabTree set taborder=(select ord from #tabs where cast(#tabs.tabID as int)=#TabTree.tabID) -- Return Temporary Table SELECT tabID, parenttabID, tabname, TabOrder, NestLevel FROM #TabTree order by TreeOrder GO这是Rainbow里面的一个存储过程生成的就是你要的那种结构的树!你有兴趣可以下载Rainbow看看,是开源的!这是它用来布置页签的树状显示的存储过程!
(
@PortalID int
)
AS
--Create a Temporary table to hold the tabs for this query
CREATE TABLE #TabTree
(
[TabID] [int],
[TabName] [nvarchar] (100),
[ParentTabID] [int],
[TabOrder] [int],
[NestLevel] [int],
[TreeOrder] [varchar] (1000)
)
SET NOCOUNT ON -- Turn off echo of "... row(s) affected"
DECLARE @LastLevel smallint
SET @LastLevel = 0
-- First, the parent levels
INSERT INTO #TabTree
SELECT TabID,
TabName,
ParentTabID,
TabOrder,
0,
cast(100000000 + taborder as varchar)
FROM rb_Tabs
WHERE ParentTabID IS NULL and PortalID =@PortalID
ORDER BY TabOrder
-- Next, the children levels
WHILE (@@rowcount > 0)
BEGIN
SET @LastLevel = @LastLevel + 1
INSERT #TabTree (TabID, TabName, ParentTabID, TabOrder, NestLevel, TreeOrder)
SELECT rb_Tabs.TabID,
Replicate('-', @LastLevel *2) + rb_Tabs.TabName,
rb_Tabs.ParentTabID,
rb_Tabs.TabOrder,
@LastLevel,
cast(#TabTree.TreeOrder as varchar) + '.' + cast(100000000 + rb_Tabs.TabOrder as varchar)
FROM rb_Tabs join #TabTree on rb_Tabs.ParentTabID= #TabTree.TabID
WHERE EXISTS (SELECT 'X' FROM #TabTree WHERE TabID = rb_Tabs.ParentTabID AND NestLevel = @LastLevel - 1)
and PortalID =@PortalID
ORDER BY #TabTree.TabOrder
END
--Get the Orphans
INSERT #TabTree (TabID, TabName, ParentTabID, TabOrder, NestLevel, TreeOrder)
SELECT rb_Tabs.TabID,
'(Orphan)' + rb_Tabs.TabName,
rb_Tabs.ParentTabID,
rb_Tabs.TabOrder,
999999999,
'999999999'
FROM rb_Tabs
WHERE NOT EXISTS (SELECT 'X' FROM #TabTree WHERE TabID = rb_Tabs.TabID)
and PortalID =@PortalID
-- Reorder the tabs by using a 2nd Temp table and an identity field to keep them straight.
select IDENTITY(int,1,2) as ord , cast(tabID as varchar) as tabID into #tabs
from #TabTree
order by nestlevel, Treeorder
-- Change the taborder in the sirt temp table so that tabs are ordered in sequence
update #TabTree set taborder=(select ord from #tabs where cast(#tabs.tabID as int)=#TabTree.tabID)
-- Return Temporary Table
SELECT tabID, parenttabID, tabname, TabOrder, NestLevel
FROM #TabTree
order by TreeOrder
GO这是Rainbow里面的一个存储过程生成的就是你要的那种结构的树!你有兴趣可以下载Rainbow看看,是开源的!这是它用来布置页签的树状显示的存储过程!