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

竹里

发布时间：2026-10-14 | 2人浏览过

原创

在处理大数据量查询时，SQL Server 早期版本的分页方式效率较低。本文介绍一种适用于 SQL Server 2005 及以上版本的通用分页存储过程，通过 ROW_NUMBER() 函数实现高效的数据截取，同时支持动态参数传入以应对不同业务需求。

背景与挑战

在 SQL Server 2000 时代，常见的分页方式（如 TOP/NOT IN 或 ID 范围筛选）随着数据量突破十万级，性能下降明显。从 SQL Server 2005 开始，引入了 ROW_NUMBER() 窗口函数，为高效分页提供了基础。该函数可以为结果集中的每一行分配一个唯一的序号，从而简化了区间查询的逻辑。

通用分页存储过程实现

为了实现复用性，我们创建一个名为 [dbo].[Common_GetPagedList] 的存储过程。它允许调用者动态指定表名、字段、排序规则和过滤条件。

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

首先确定输入和输出参数，确保灵活性：

@TableName: 目标表名 (nvarchar(100))。
@ColumnNames: 需要查询的字段，默认 * 表示所有字段。
@OrderClause: 排序条件，不包含 ORDER BY 关键字。
@WhereClause: 过滤条件，不包含 WHERE 关键字，默认为 1=1 以简化拼接。
@PageSize: 每页大小，若为 0 则返回所有数据。
@PageIndex: 当前页码，从 1 开始。
@TotalRecord: 输出参数，返回符合查询条件的总记录数。

复制代码代码如下:

CREATE PROCEDURE [dbo].[Common_GetPagedList]
(
    @TableName nvarchar(100),          -- 表名
    @ColumnNames nvarchar(1000) = '*', -- 字段名集合(全部字段为*,其它逗号分隔)
    @OrderClause nvarchar(1000),       -- 排序从句(不包含order by)
    @WhereClause nvarchar(1000) = N' 1=1 ', -- 条件从句(不包含where)
    @PageSize int = 0,                 -- 每页记录数(0为所有)
    @PageIndex int = 1,                -- 页索引(从1开始)
    @TotalRecord int output            -- 返回总记录数
)
AS

第2步：处理默认值与空值逻辑

在构建 SQL 字符串前，对可能的 NULL 或空字符串参数进行防御性处理，确保生成的 SQL 语法正确：

BEGIN 
    if (@ColumnNames is null or @ColumnNames='') set @ColumnNames=' * '
    if (@WhereClause is null or @WhereClause='') set @WhereClause=' 1=1 '
    if (@OrderClause is null or @OrderClause='') set @OrderClause=' Id desc '

第3步：计算总记录数

使用 sp_executesql 执行动态 SQL 来获取总行数，以便前端显示分页信息。注意此处需将变量作为参数传递以避免注入风险，尽管在此示例中主要关注功能逻辑：

--处理开始点和结束点
Declare @StartRecord int;
Declare @EndRecord int; 
Declare @TotalCountSql nvarchar(1200); 
Declare @SqlString nvarchar(4000);   

--统计记录
if(@TotalRecord is null OR @TotalRecord>=0)
begin
    SET @TotalCountSql= N'select @TotalRecord = count(*) from ' + @TableName +' where '+@WhereClause; 
    EXEC sp_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord output;--返回总记录数
end

第4步：构建分页查询语句

根据 @PageSize 判断是进行分页查询还是全量查询。分页时，先利用 ROW_NUMBER() 生成序号列，再在外层查询中截取指定区间的数据：

if @PageSize>0
begin   
    if @PageIndex<1 set @PageIndex=1
    set @StartRecord = (@PageIndex-1)*@PageSize + 1      
    set @EndRecord = @StartRecord + @PageSize - 1 
    
    -- 核心分页逻辑：使用 ROW_NUMBER()
    set @SqlString = N'select row_number() over (order by '+ @OrderClause +') as rowId,'+@ColumnNames+' from '+ @TableName+' where '+@WhereClause;
    
    -- 外层筛选 rowId 范围
    set @SqlString ='select * from (' + @SqlString + ') as t where rowId between ' + ltrim(str(@StartRecord)) + ' and ' + ltrim(str(@EndRecord));
end
else 
begin
    -- 不分页情况，直接查询
    set @SqlString='select '+@ColumnNames+' from '+ @TableName+' where '+@WhereClause +' order by '+@OrderClause
end

Exec(@SqlString)
END

使用注意事项

性能考量：虽然 ROW_NUMBER() 比旧式方法快，但对于超大数据集，建议在 @OrderClause 中使用的字段建立索引，并在 @WhereClause 中覆盖常用查询条件。
SQL 注入：本存储过程使用动态 SQL 拼接，调用方需确保传入的表名、字段和条件来自可信来源或经过严格校验，防止 SQL 注入攻击。
兼容性：此语法适用于 SQL Server 2005 及更高版本（包括 SQL Server 2008, 2012+ 等）。

总结

上述通用存储过程提供了一种灵活且相对高效的 SQL Server 分页方案。通过结合 ROW_NUMBER() 和动态 SQL，它能够适应多种查询场景。以上就是基于 Sql Server 通用分页存储过程的解决方法的详细内容，更多关于 SQL Server 分页优化与存储过程编写技巧的资料请关注本站其它相关文章！