SQL Server 分頁預存程序：支援多欄位排序的通用方案

預存程序完整碼

CREATE PROCEDURE GetRecordByPage
    @sTable      NVARCHAR(100),      -- 表名
    @sPkey       NVARCHAR(50),       -- 主鍵 (必須有)
    @sField      NVARCHAR(1000) = '*', -- 查詢欄位
    @iPageCurr   INT,                -- 目前頁碼
    @iPageSize   INT,                -- 每頁筆數
    @sCondition  NVARCHAR(1000),     -- WHERE 條件 (不含 WHERE)
    @sOrder      NVARCHAR(100),      -- ORDER BY 內容 (含 ASC/DESC)
    @Counts      INT = 0 OUTPUT,     -- 總筆數
    @pageCount   INT = 1 OUTPUT      -- 總頁數
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sC1 NVARCHAR(1000), @sC2 NVARCHAR(1000);
    DECLARE @iAsc INT, @iDesc INT, @iType TINYINT;
    DECLARE @sT1 NVARCHAR(1000), @sT2 NVARCHAR(1000), @sT3 NVARCHAR(1000), @sT4 NVARCHAR(1000), @sSQL NVARCHAR(4000);

    /* 處理 WHERE 條件 */
    IF LEN(@sCondition) > 2
        SELECT @sC1 = ' WHERE ' + @sCondition + ' ', @sC2 = ' WHERE ' + @sCondition + ' AND ';
    ELSE
        SELECT @sC1 = '', @sC2 = ' WHERE ';

    /* 解析排序方向判斷游標/Key-set 分頁策略 */
    SELECT @sT1 = UPPER(@sOrder), @sT2 = @sOrder, @iType = 0, @sOrder = '', @sT4 = UPPER(@sPkey);
    IF LEN(@sT2) > 2
    BEGIN
        SELECT @iAsc = CHARINDEX('ASC', @sT1), @iDesc = CHARINDEX('DESC', @sT1);
        IF @sT4 = SUBSTRING(@sT1, 1, LEN(@sT4))  -- 主鍵在排序首欄
        BEGIN
            IF (@iAsc > 0 AND @iDesc = 0) OR (@iAsc > 0 AND @iDesc > 0 AND @iAsc < @iDesc)
                SELECT @iType = 1, @sT3 = '> (SELECT MAX(';  -- 遞增
            ELSE IF (@iAsc = 0 AND @iDesc > 0) OR (@iAsc > 0 AND @iDesc > 0 AND @iAsc > @iDesc)
                SELECT @iType = 1, @sT3 = '< (SELECT MIN(';  -- 遞減
        END
        SET @sOrder = ' ORDER BY ' + @sT2;
    END

    /* 取得總筆數 */
    SET @sSQL = 'SELECT @Counts = COUNT(*) FROM ' + @sTable + @sC1;
    EXEC sp_executesql @sSQL, N'@Counts INT OUTPUT', @Counts OUTPUT;
    SET @pageCount = (@Counts + @iPageSize - 1) / @iPageSize;
    IF @iPageCurr > @pageCount SET @iPageCurr = @pageCount;
    SELECT @iPageCurr = CASE 
        WHEN @Counts < (@iPageCurr - 1) * @iPageSize THEN CEILING(@Counts * 1.0 / @iPageSize)
        WHEN @iPageCurr < 1 THEN 1 ELSE @iPageCurr END;

    /* 動態產生分頁查詢 */
    IF @iPageCurr > 1 AND @iType = 1
        SET @sSQL = 'SELECT TOP ' + CAST(@iPageSize AS NVARCHAR) + ' ' + @sField + 
            ' FROM ' + @sTable + @sC2 + @sPkey + @sT3 + @sPkey + ') FROM (SELECT TOP ' + 
            CAST((@iPageCurr - 1) * @iPageSize AS NVARCHAR) + ' ' + @sPkey + 
            ' FROM ' + @sTable + @sC1 + @sOrder + ') AS tbTemp) ' + @sOrder;
    ELSE IF @iPageCurr > 1 AND @iType = 0
        SET @sSQL = 'SELECT ' + @sField + ' FROM ' + @sTable + @sC2 + @sPkey + 
            ' IN (SELECT TOP ' + CAST(@iPageSize AS NVARCHAR) + ' ' + @sPkey + 
            ' FROM ' + @sTable + @sC2 + @sPkey + ' NOT IN (SELECT TOP ' + 
            CAST((@iPageCurr - 1) * @iPageSize AS NVARCHAR) + ' ' + @sPkey + 
            ' FROM ' + @sTable + @sC1 + @sOrder + ') ' + @sOrder + ') ' + @sOrder;
    ELSE
        SET @sSQL = 'SELECT TOP ' + CAST(@iPageSize AS NVARCHAR) + ' ' + @sField + 
            ' FROM ' + @sTable + @sC1 + @sOrder;

    EXEC (@sSQL);
    PRINT @sSQL;  -- 偵錯用
END
GO

呼叫範例

DECLARE @total INT, @pages INT;
EXEC GetRecordByPage 
    'Orders', 'OrderID', 'OrderID, CustomerID, OrderDate', 
    3, 20, 'CustomerID = ''ALFKI''', 'OrderDate DESC', 
    @total OUTPUT, @pages OUTPUT;
SELECT @total AS TotalRows, @pages AS TotalPages;

核心策略說明

• Key-set 分頁：排序首欄為主鍵且單一方向 (ASC/DESC) 時，用 > MAX(...) 或 < MIN(...) 避免掃描前 N 頁。
• NOT IN 分頁：多欄位排序或非主鍵排序時退回相容模式。
• 動態 SQL：支援任意表、欄位、條件、排序，極具彈性。

現代替代方案 (SQL 2012+)

SELECT * FROM Orders
WHERE CustomerID = 'ALFKI'
ORDER BY OrderDate DESC
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;  -- 第 3 頁, 每頁 20 筆

新專案請優先使用 OFFSET/FETCH，語法標準、效能穩定、免維護動態 SQL。