存儲過程實現無限級分類（一）

無限級分類資料表設計與新增預存程序

資料表結構：Tb_Column

Column_ID int PRIMARY KEY (非 IDENTITY)
Column_Name nvarchar(50) NOT NULL
Parent_ID int NOT NULL DEFAULT 0
Column_Path nvarchar(1000) NOT NULL
Column_Depth int NOT NULL DEFAULT 0
Column_Order int NOT NULL DEFAULT 0
Column_Intro nvarchar(1000) NOT NULL

關鍵欄位說明：

• Column_Path：如 1|5|12 表示從根節點到本節點的路徑，利於遞迴查詢與排序。
• Column_Depth：層級深度，根節點為 1。
• Column_Order：同層顯示順序。

新增預存程序：sp_Column_Insert

CREATE PROCEDURE sp_Column_Insert
(
    @Parent_ID int,
    @Column_Name nvarchar(50),
    @Column_Intro nvarchar(1000)
)
AS
DECLARE @Err AS int, @Column_ID AS int, @Column_Depth AS int
SET @Err = 0
BEGIN TRAN

-- 取得新 Column_ID (MAX+1)
SELECT @Column_ID = MAX(Column_ID) FROM Tb_Column
SET @Column_ID = ISNULL(@Column_ID, 0) + 1

DECLARE @Column_Path AS nvarchar(1000), @Column_Order AS int

IF @Parent_ID = 0  -- 根節點
BEGIN
    SET @Column_Path = LTRIM(STR(@Column_ID))
    SELECT @Column_Order = MAX(Column_Order) FROM Tb_Column
    SET @Column_Order = ISNULL(@Column_Order, 0) + 1
    SET @Column_Depth = 1
END
ELSE  -- 子節點
BEGIN
    SELECT @Column_Path = Column_Path, @Column_Depth = Column_Depth FROM Tb_Column WHERE Column_ID = @Parent_ID
    IF @Column_Path IS NULL
    BEGIN SET @Err = 1; GOTO theEnd; END

    -- 同父節點下最大 Order
    SELECT @Column_Order = MAX(Column_Order) FROM Tb_Column WHERE Column_Path LIKE @Column_Path + '|%' OR Column_ID = @Parent_ID
    IF @Column_Order IS NOT NULL
    BEGIN
        UPDATE Tb_Column SET Column_Order = Column_Order + 1 WHERE Column_Order > @Column_Order
        SET @Column_Order = @Column_Order + 1
    END
    ELSE
    BEGIN SET @Err = 1; GOTO theEnd; END

    SET @Column_Path = @Column_Path + '|' + LTRIM(STR(@Column_ID))
    SET @Column_Depth = @Column_Depth + 1
END

INSERT INTO Tb_Column (Column_Name, Parent_ID, Column_Path, Column_Depth, Column_Order, Column_Intro)
VALUES (@Column_Name, @Parent_ID, @Column_Path, @Column_Depth, @Column_Order, @Column_Intro)

IF @@ERROR <> 0 SET @Err = 1

theEnd:
IF @Err = 0 BEGIN COMMIT TRAN; RETURN @Column_ID; END
ELSE BEGIN ROLLBACK TRAN; RETURN 0; END
GO

核心邏輯

1. 以 MAX(Column_ID)+1 手動分配主鍵，避免 IDENTITY 導致間隙。
2. 根節點：Path=ID、Depth=1、Order=MAX+1。
3. 子節點：繼承父 Path 加上自身 ID、Depth+1、同父最大 Order+1 並將後續節點 Order+1 騰出空位。
4. 事務保證原子性，錯誤即回滾。

此設計讓讀取只需單一 ORDER BY Column_Path 或 Column_Order 即可獲得完整階層順序，極大簡化查詢邏輯。