這一段 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]
*/
全站熱搜
留言列表