-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Wyhw Whon>
-- Create date: <2007-07-26>
-- Description: <一个ASP.NET菜单控件Beta 0.1的SQL复杂实现>
-- =============================================
ALTER PROCEDURE [dbo].[GetMenu]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
CREATE TABLE #TempMenuLinks([Name][nvarchar] (50),HavChild [int],[ID] [int],[Image] [nvarchar] (50),Target [nvarchar] (50),URL [nvarchar] (250),ParentID [int])
INSERT INTO #TempMenuLinks SELECT [Name] = '家园首页',HavChild = -1,[ID] = 0,
[Image] = '',Target='_blank' ,URL = 'http://www.sinohot.net',
ParentID = -1
INSERT INTO #TempMenuLinks SELECT [Name] = '新手上路',HavChild = 0,[ID] = 0,
[Image] = 'ltimages/bbsserver.gif',Target='_blank' ,URL = 'http://help.sinohot.net',
ParentID = -1
INSERT INTO #TempMenuLinks SELECT [Name] = '精华热图',HavChild = 0,[ID] = 0,
[Image] = 'ltimages/HOT.gif',Target='_blank' ,URL = 'http://www.sinohot.net/MoreIndex/picmore.html',
ParentID = -1
DECLARE @Groups_Cur CURSOR
SET @Groups_Cur = CURSOR LOCAL SCROLL FOR
SELECT [GID] AS ID,[Name],
URL = 'http://forum.sinohot.net/group.aspx?groupid=' + CAST(G.[GpID] AS NVARCHAR(10)) + '&scope=groups'
FROM Groups G (nolock) ORDER By [SortOrder]
OPEN @Groups_Cur
DECLARE @ID INT
DECLARE @Name NVARCHAR(50)
DECLARE @URL NVARCHAR(250)
IF Cursor_Status('variable', '@Groups_Cur') > 0
BEGIN
--FETCH @my_lakes_cursor INTO
FETCH NEXT FROM @Groups_Cur INTO @ID,@Name,@URL
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #TempMenuLinks SELECT [Name] = @Name,HavChild = 1,[ID] = @ID,
[Image] = '',Target='main' ,URL = @URL,
ParentID = -1
INSERT INTO #TempMenuLinks
SELECT F.[Name],HavChild = 0,F.[SID] AS ID,
[Image] = '',Target='main',
URL = 'http://forum.sinohot.net/forum.aspx?forumid=' + CAST(F.[SID] AS NVARCHAR(10)) + '&scope=threads',
ParentID = @ID
FROM SubMenu F (nolock)
WHERE F.[GID] = @ID
AND F.IsActive = 1
ORDER BY F.[SortOrder]
FETCH NEXT FROM @Groups_Cur INTO @ID,@Name,@URL
END
END
CLOSE @Groups_Cur
DEALLOCATE @Groups_Cur
INSERT INTO #TempMenuLinks SELECT [Name] = '退出论坛',HavChild = 0,[ID] = 0,
[Image] = 'ltimages/exit.gif',Target='main' ,URL = 'http://forum.sinohot.net/logoff.aspx?r=%2fforumheader.aspx%3frefresh=1',
ParentID = -1
SELECT * FROM #TempMenuLinks
DROP TABLE #TempMenuLinks
END
GO