分頁核心邏輯

SQL Server 2005+ 使用 ROW_NUMBER() OVER (ORDER BY ...) 配合 BETWEEN 篩選頁碼範圍；SQL Server 2000 需用雙重 TOP 或游標模擬。

版本一：SQL 2005 基礎版

CREATE PROC dbo.usp_Page2005
    @TableName VARCHAR(50),
    @Fields VARCHAR(5000) = '*',
    @OrderField VARCHAR(5000),
    @SqlWhere VARCHAR(5000) = NULL,
    @PageSize INT,
    @PageIndex INT = 1,
    @TotalPage INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(4000), @TotalRecord INT;

    -- 1. 計算總筆數
    SET @SQL = 'SELECT @TotalRecord = COUNT(*) FROM ' + @TableName +
               CASE WHEN @SqlWhere IS NULL OR @SqlWhere = '' THEN '' ELSE ' WHERE ' + @SqlWhere END;
    EXEC sp_executesql @SQL, N'@TotalRecord INT OUTPUT', @TotalRecord OUTPUT;

    SET @TotalPage = CEILING(@TotalRecord * 1.0 / @PageSize);
    SET @PageIndex = CASE WHEN @PageIndex <= 0 THEN 1 WHEN @PageIndex > @TotalPage THEN @TotalPage ELSE @PageIndex END;

    -- 2. 組裝分頁查詢
    DECLARE @Start INT = (@PageIndex - 1) * @PageSize + 1;
    DECLARE @End INT = @Start + @PageSize - 1;

    SET @SQL = 'SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY ' + @OrderField + ') AS RowId, ' + @Fields +
               ' FROM ' + @TableName +
               CASE WHEN @SqlWhere IS NULL OR @SqlWhere = '' THEN '' ELSE ' WHERE ' + @SqlWhere END +
               ') AS T WHERE RowId BETWEEN ' + CAST(@Start AS VARCHAR) + ' AND ' + CAST(@End AS VARCHAR);

    EXEC (@SQL);
    RETURN @TotalRecord;
END;

版本二：SQL 2005 優化版 (支援外部傳入總筆數，避免重複 COUNT)

CREATE PROC dbo.usp_Page2005_V2
    @TableName VARCHAR(50),
    @Fields VARCHAR(5000) = '*',
    @OrderField VARCHAR(5000),
    @SqlWhere VARCHAR(5000) = NULL,
    @PageSize INT,
    @PageIndex INT = 1,
    @TotalRecord INT = 0,   -- 外部可傳入，避免重算
    @TotalPage INT OUTPUT
AS
BEGIN
    -- 同版本一，但若 @TotalRecord > 0 則跳過 COUNT 查詢
    -- 適合「先查總數、再分頁」的雙步驟架構
END;

版本三：支援 JOIN 多表查詢

CREATE PROC dbo.usp_Page2005_Join
    @TableName VARCHAR(150),  -- 可包含 JOIN 子句，如 "TableA a JOIN TableB b ON a.Id=b.AId"
    @Fields VARCHAR(5000) = '*',
    @OrderField VARCHAR(5000),
    @SqlWhere VARCHAR(5000) = NULL,
    @PageSize INT,
    @PageIndex INT = 1,
    @TotalRecord INT = 0,
    @TotalPage INT OUTPUT
AS
BEGIN
    -- 邏輯同 V2，但 @TableName 允許複雜 FROM 子句
    -- 注意：ORDER BY 欄位需加表別名限定
END;

版本四：SQL 2000 相容版 (雙重 TOP 模擬)

CREATE PROC dbo.usp_Page2000
    @TableName VARCHAR(50),
    @Fields VARCHAR(5000) = '*',
    @OrderField VARCHAR(5000),
    @SqlWhere VARCHAR(5000) = NULL,
    @PageSize INT,
    @PageIndex INT = 1,
    @TotalPage INT OUTPUT
AS
BEGIN
    DECLARE @SQL NVARCHAR(4000), @TotalRecord INT, @Start INT, @End INT;
    -- 計算總筆數 (同 2005)
    -- 分頁邏輯：
    SET @Start = (@PageIndex - 1) * @PageSize;
    SET @SQL = 'SELECT TOP ' + CAST(@PageSize AS VARCHAR) + ' ' + @Fields +
               ' FROM (SELECT TOP ' + CAST(@Start + @PageSize AS VARCHAR) + ' ' + @Fields +
               ' FROM ' + @TableName +
               CASE WHEN @SqlWhere IS NULL OR @SqlWhere = '' THEN '' ELSE ' WHERE ' + @SqlWhere END +
               ' ORDER BY ' + @OrderField + ') AS T ORDER BY ' + REPLACE(@OrderField, 'ASC', 'DESC') + ' -- 反向排序取尾段';
    EXEC (@SQL);
END;

進階：二分法分頁 (適合超大資料集、深分頁)

-- 核心思想：利用叢集索引鍵值定位，避免掃描大量前置列
DECLARE @MinKey INT, @MaxKey INT;
SELECT @MinKey = MIN(Id), @MaxKey = MAX(Id) FROM BigTable;

-- 二分搜尋定位第 N 頁起始鍵值
WHILE @MinKey < @MaxKey
BEGIN
    DECLARE @Mid INT = (@MinKey + @MaxKey) / 2;
    DECLARE @Cnt INT = (SELECT COUNT(*) FROM BigTable WHERE Id <= @Mid);
    IF @Cnt < @TargetStartRow
        SET @MinKey = @Mid + 1;
    ELSE
        SET @MaxKey = @Mid;
END
-- @MinKey 即為該頁起始 Id，再 SELECT TOP @PageSize WHERE Id >= @MinKey ORDER BY Id;

適用於單調遞增主鍵 (IDENTITY) 的大表深分頁 (如第 10000 頁)，效能優於 ROW_NUMBER 全掃描。

使用建議

• SQL 2005+ 標準場景 → 版本一/二 (ROW_NUMBER)
• 需 JOIN 多表 → 版本三
• SQL 2000 環境 → 版本四
• 千萬列大表深分頁 → 二分法 或 Keyset Pagination (Seek 法)
• 前端分頁建議改用 Keyset Pagination (WHERE Id > @LastSeenId ORDER BY Id LIMIT N)，完全避免 OFFSET 掃描。