這一段 SQL Statment 是為了套用javascript實做出 Menu 的巡覽列。之前都是在 Code Behind 的地方用遞迴去組 html，這次突然想用 SQL 的 FOR XML 來做看看，於是寫了這段出來。

    本來是想用 CTE 做看看，但仔細想想，CTE 似乎做不到這種感覺，所以最後還是用 FUNCTION 去做遞迴的效果。

    直接執行就可以看到效果了，有興趣的朋友不妨複製下去跑看看！

  

CREATE TABLE [dbo].[Pages](
    PageID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    PageName NVARCHAR(50) NOT NULL DEFAULT '',
    [Action] VARCHAR(50) NOT NULL DEFAULT '',
    Controller VARCHAR(50) NOT NULL DEFAULT '',
    ParentID INT NOT NULL DEFAULT 0,
    Sort TINYINT NOT NULL DEFAULT 1
)
GO 
INSERT INTO dbo.Pages VALUES
('檔案','','',0,1),
('連接物件總管','Connect','File',1,1),
('中斷與物件總管的連接','Disconnect','File',1,2),
('新增','Create','File',1,3),
('專案','Project','File',4,1),
('使用目前的連接查詢','Search','File',4,2),
('編輯','','',0,2),
('復原','Undo','Edit',7,1),
('取消復原','Restore','Edit',7,2),
('檢視','','',0,3),
('物件總管','Explorer','View',10,1),
('查詢','Search','Query',0,4)
GO
CREATE FUNCTION [dbo].[f_GetMenuRecursive]
(
    @parentID INT 
)
RETURNS XML
AS
BEGIN 
    DECLARE @value XML 
    
    IF EXISTS(
        SELECT * 
        FROM dbo.Pages
        WHERE ParentID = @parentID
    )
    BEGIN 
        SELECT @value = (
            SELECT  'nfSubC nfSubS' AS '@class',
                    CONVERT(XML,(
                        SELECT  'nfItem' AS '@class',
                                ISNULL('/' + NULLIF(Controller,'') 
                                + '/' + 
                                NULLIF([Action],''),
                                'javascript:void(0)') 
                                AS 'a/@href',
                                'nfLink' AS 'a/@class',
                                PageName AS a,
                                dbo.f_GetMenuRecursive(PageID)
                        FROM dbo.Pages
                        WHERE ParentID = @parentID
                        ORDER BY Sort
                        FOR xml PATH('li')
                    ) )
            FOR XML PATH('ul')
        )
    END
    
    RETURN (@value)
END 
GO 
CREATE PROCEDURE [dbo].[s_GetMenu] 
 
AS
BEGIN
    SET NOCOUNT ON 
    
    SELECT  'nfItem' AS '@class',
            ISNULL('/' + NULLIF(Controller,'') + '/' + 
            NULLIF([Action],''),'javascript:void(0)') AS 'a/@href',
            'nfLink' AS 'a/@class',
            PageName AS a,
            dbo.f_GetMenuRecursive(PageID)
    FROM dbo.Pages
    WHERE ParentID = 0
    ORDER BY Sort
    FOR xml PATH('li'),ROOT('ul')
END 
GO 
 
/*
    測試 sp 的執行結果
    EXEC [s_GetMenu]
    
    刪除剛建立的資料庫物件
    DROP FUNCTION dbo.[f_GetMenuRecursive]
    DROP PROCEDURE [dbo].[s_GetMenu]
    DROP TABLE [dbo].[Pages]
*/
文章標籤
全站熱搜
創作者介紹
創作者 AnferneeHardaway 的頭像
AnferneeHardaway

阿尼

AnferneeHardaway 發表在 痞客邦 留言(0) 人氣(601)