SQL Server 通用存储过程分页实现方案

功能目标与适用范围

在大型 Web 应用中，直接执行 LIMIT/OFFSET（MySQL）或 ROW_NUMBER()（SQL Server 2012+）可能无法完全满足对旧版本兼容性或特定性能优化的需求。此存储过程采用经典的 “Top N” 嵌套查询逻辑，主要解决以下场景：

• 需要在 SQL Server 环境下实现高性能的分页查询。
• 希望复用同一套代码处理不同表的分页，避免硬编码 SQL。
• 需要同时获取总记录数（Count）和当前页数据（Select）。

准备条件

1. 数据库环境：必须为 Microsoft SQL Server（适用于 2005 及以上版本，语法兼容性较好）。
2. 权限要求：执行用户需具备相应表的 SELECT 权限以及创建/修改存储过程的权限（若初次部署）。
3. 索引建议：为保证分页性能，用于排序的字段 @fldName 应当建立索引，尤其是当 @PageIndex 较大时，能显著减少扫描开销。

核心操作详解

第1步：定义存储过程参数

进入数据库管理工具（如 SSMS），新建存储过程脚本。以下是关键参数说明：

• @tblName (varchar)：目标数据表名。
• @strGetFields (varchar)：需返回的列名，默认 '*' 表示全部字段。为提升性能，建议明确指定需要的列。
• @fldName (varchar)：用于排序的字段名，默认 'id'。
• @PageSize (int)：每页显示的数据条数，默认为 10。
• @PageIndex (int)：当前页码，默认为 1。
• @doCount (bit)：开关参数。非 0 值则只返回总记录数，不返回具体数据；为 0 时返回数据列表。
• @OrderType (bit)：排序类型。0 为升序 (ASC)，1 为降序 (DESC)。
• @strWhere (varchar)：查询条件字符串。注意：此处不要包含关键字 WHERE，例如传入 'status=1 AND age > 18' 即可。
• @ID (nvarchar)：主键字段名，用于内部 ID 范围判断，确保分页连续性。

第2步：解析与统计总数逻辑

存储过程首先判断 @doCount 的值。如果业务仅需要知道总页数（通常前端分页控件需要），可仅调用此部分：

if @doCount != 0 begin
    if @strWhere !=''
        set @strSQL = 'select count(*) as Total from ' + @tblName+ ' where '+@strWhere
    else
        set @strSQL = 'select count(*) as Total from ' + @tblName + ''
end

这段逻辑通过动态拼接 SELECT COUNT(*) 语句并执行，快速得出符合条件的总行数。

第3步：构建分页查询主体

当不需要仅统计数量时，进入数据获取分支。逻辑分为两步：

1. 确定排序方向：

if @OrderType != 0 begin
    set @strTmp = '<(select min'
    set @strOrder = ' order by ' + @fldName +' desc'
end
else begin
    set @strTmp = '>(select max'
    set @strOrder = ' order by ' + @fldName +' asc'
end

这里利用 < 和 > 配合子查询来确定“上一页最大/最小 ID”，从而截取当前页数据。这是经典的高性能分页技巧，避免了全表扫描。

2. 区分第一页与后续页：

• 第一页优化：若 @PageIndex = 1，直接使用 SELECT TOP (@PageSize) 配合排序条件。这利用了数据库的最早返回特性，速度最快。
• 非第一页逻辑：构造三层嵌套查询。内层查询选出前 (PageIndex-1)*PageSize 条记录的排序字段，外层再从中取最小/最大值作为边界，最终选取当前页的 @PageSize 条记录。

-- 带条件的一般情况示例
set @strSQL = 'select top '+ str(@PageSize) +' '+@strGetFields+' from '
+ @tblName + ' where ' + @fldName + ' '+ @strTmp + '('
+ @ID +') from (select top '+ str((@PageIndex-1)*@PageSize) + ' '
+ @fldName + ' from ' + @tblName + ' where '+ @strWhere + ' '
+ @strOrder + ') as tblTmp) and '+ @strWhere + ' '+ @strOrder

第4步：执行动态 SQL

所有参数拼接完毕后，使用系统存储过程执行生成的字符串：

exec (@strSQL)

完成判断与验证

存储过程创建成功后，可通过以下方式测试：

1. 获取总数：
   EXEC page 'YourTableName', '*', 'id', 10, 1, 1, 0, '', 'id'
   预期结果：返回一行数据，包含 Total 列的数值。
2. 获取第二页数据：
   EXEC page 'YourTableName', 'col1,col2', 'id', 10, 2, 0, 0, 'active_flag=1', 'id'
   预期结果：返回第 11-20 条符合 active_flag=1 条件的记录，并按 id 升序排列。

简要总结

上述关于 SQL Server 存储过程分页的实现方案，涵盖了从参数定义、逻辑分支到动态执行的全过程。这种方法在处理复杂条件和传统 SQL Server 版本时具有良好的兼容性与性能表现。以上就是 SQL 存储过程分页代码支持亿万庞大数据量处理的详细内容，更多关于 SQL 优化及存储过程开发的资料请关注本站其它相关文章！