SQL Server 2000 自訂排序交換邏輯：以 OrderNum 欄位實現上下移動

在 CMS、產品目錄、選單管理等系統中，常需提供「上移/下移」按鈕讓使用者自訂顯示順序。本文展示一套以 OrderNum 整數欄位為排序依據、透過預存程序實現記錄交換的完整方案，並處理相同 OrderNum 時以 ID 遞減為次序的邊界條件。

資料結構與排序規則

CREATE TABLE Sorts (
    ID       INT PRIMARY KEY,
    Name     NVARCHAR(100),
    OrderNum INT NOT NULL
);

-- 排序規則：OrderNum DESC, ID DESC

前端呼叫範例

GridView/Repeater 中的上下移動連結：

<a href="SortHandler.ashx?order=up&id=<%# Eval("ID") %>&type=<%# Eval("OrderNum") %>">↑</a> |
<a href="SortHandler.ashx?order=down&id=<%# Eval("ID") %>&type=<%# Eval("OrderNum") %>">↓</a>

核心預存程序：NewsSortOrder

CREATE PROCEDURE dbo.NewsSortOrder
    @Order VARCHAR(10),  -- 'up' 或 'down'
    @ID    INT,          -- 目前記錄 ID
    @Type  INT           -- 目前記錄 OrderNum
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @NeighborID INT;

    IF @Order = 'up'  -- 向上：尋找 OrderNum >= @Type 且 ID <> @ID 的最小 OrderNum（最接近的上一筆）
    BEGIN
        SELECT TOP 1 @NeighborID = ID
        FROM dbo.Sorts
        WHERE OrderNum >= @Type AND ID <> @ID
        ORDER BY OrderNum ASC, ID ASC;  -- 同 OrderNum 取較小 ID（因主排序 ID DESC，上一筆 ID 較大）

        IF @NeighborID IS NOT NULL
        BEGIN
            DECLARE @NeighborType INT = (SELECT OrderNum FROM dbo.Sorts WHERE ID = @NeighborID);
            IF @NeighborType = @Type
            BEGIN
                -- 同組：將目前記錄 OrderNum +1，並將其上方所有同組記錄 +1，騰出空位
                UPDATE dbo.Sorts SET OrderNum = OrderNum + 1 WHERE ID = @ID OR (OrderNum > @Type);
            END
            ELSE
            BEGIN
                -- 不同組：直接交換 OrderNum
                UPDATE dbo.Sorts SET OrderNum = @NeighborType WHERE ID = @ID;
                UPDATE dbo.Sorts SET OrderNum = @Type WHERE ID = @NeighborID;
            END
        END
    END
    ELSE IF @Order = 'down'  -- 向下：尋找 OrderNum <= @Type 且 ID <> @ID 的最大 OrderNum
    BEGIN
        SELECT TOP 1 @NeighborID = ID
        FROM dbo.Sorts
        WHERE OrderNum <= @Type AND ID <> @ID
        ORDER BY OrderNum DESC, ID DESC;

        IF @NeighborID IS NOT NULL
        BEGIN
            DECLARE @NeighborType INT = (SELECT OrderNum FROM dbo.Sorts WHERE ID = @NeighborID);
            IF @NeighborType = @Type
            BEGIN
                UPDATE dbo.Sorts SET OrderNum = OrderNum - 1 WHERE ID = @ID OR (OrderNum < @Type);
            END
            ELSE
            BEGIN
                UPDATE dbo.Sorts SET OrderNum = @NeighborType WHERE ID = @ID;
                UPDATE dbo.Sorts SET OrderNum = @Type WHERE ID = @NeighborID;
            END
        END
    END
END
GO

關鍵邏輯解析

• 同組交換：當相鄰記錄 OrderNum 相同時，單純交換會導致順序不變。採用「目前記錄 ±1，並將同組其他記錄同步位移」策略，確保視覺上真正上移/下移一位。
• 跨組交換：OrderNum 不同時，直接互換兩記錄的 OrderNum 值即可。
• 邊界保護：若已在最頂/最底（找不到鄰居），@NeighborID 為 NULL，程序不執行任何更新。
• 並發考量：高併發環境建議加上 WITH (UPDLOCK, HOLDLOCK) 或包裹在交易中，避免競爭條件導致 OrderNum 重複或遺漏。

替代方案：浮點數/間隔編號法

若移動操作極其頻繁，可考慮使用 DECIMAL(18,6) 類型的 SortOrder，移動時取兩鄰居中間值（如 10.0 與 20.0 → 15.0），避免大量 UPDATE。但需定期重新編號防止精度耗盡。

測試腳本

-- 初始資料
INSERT INTO Sorts VALUES (1, N'項目A', 1), (2, N'項目B', 2), (3, N'項目C', 2), (4, N'項目D', 3);
-- 預期排序：D(3), C(2,ID3), B(2,ID2), A(1)

-- 將 A (ID=1, OrderNum=1) 上移 → 應與 D 交換變為 OrderNum=3
EXEC NewsSortOrder 'up', 1, 1;

-- 將 C (ID=3, OrderNum=2) 下移 → 應與 B 同組位移
EXEC NewsSortOrder 'down', 3, 2;

SELECT * FROM Sorts ORDER BY OrderNum DESC, ID DESC;

此方案在 SQL Server 2000/2005/2008 均經實測可行，邏輯清晰、易於維護擴充。