MSSQL通用分页存储过程实现指南

核心逻辑与目标

传统的分页方法在不同版本和场景下效率差异较大。本存储过程采用基于主键（或唯一索引）的范围截取法。其核心思路是：

• 第一页：直接选取前 N 条满足条件的记录。
• 非第一页：先找出前 (当前页码 * 每页条数) 条记录中的最大或最小主键值，再根据此边界值截取后续的 N 条记录。

同时，为了减少客户端压力，存储过程在一次执行中不仅返回结果集，还通过第二条 SELECT 语句返回符合条件的总记录数。

准备条件

在使用此存储过程前，请确保：

1. 数据库为 Microsoft SQL Server（适用于 2005 及以上版本语法兼容区间的最佳实践，虽使用 TOP 而非 OFFSET/FETCH，但在特定关联查询场景中仍具参考价值）。
2. 被查询的表必须有一个标识主键或唯一值的字段 (@tablefield)。
3. 传入的 @where 条件需自行保证 SQL 注入安全性（建议在生产环境中通过应用层预处理或使用参数化查询替代部分动态拼接）。

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

存储过程名为 proc_CommonPaging，接收以下关键参数以控制查询行为：

• @tablename: 搜索的目标表名。
• @tablefield: 用于排序和范围判断的字段，通常为主键。
• @where: 过滤条件字符串，如 'status = 1'；显示所有记录时设为 '1=1'。
• @orderby: 排序子句，如 'ORDER BY createdate DESC'。
• @fieldlist: 需要返回的字段列表，如 'id, name, email'，使用 * 表示全选。
• @curpage: 当前页码（从 0 开始计数）。
• @page_record: 每页显示的记录条数。
• @sort: 排序标识，'asc' 表示升序，'desc' 表示降序。

第2步：内部逻辑处理

存储过程内部声明了多个变量来构建动态 SQL 字符串 @cmd：

1. 确定操作符：
   • 若为 DESC（倒序），使用 < 符号，并查找 MIN 值。
   • 若为 ASC（正序），使用 > 符号，并查找 MAX 值。
2. 计算上界记录数：@uprecord 等于 @curpage * @page_record。
3. 构建查询命令：
   • 第一页情况：当 @curpage = 0 时，直接拼接 SELECT TOP @page_record ... FROM ... WHERE ... ORDER BY ...。
   • 其他页情况：嵌套子查询。首先选取前 @uprecord 条记录，从中取出边界主键值，然后在外层查询中筛选出超出此边界且符合原条件的剩余 @page_record 条记录。
4. 附加总数查询：在命令末尾追加 ; SELECT COUNT(*) FROM ... WHERE ...，以便一次性获取数据量和总数。

第3步：执行存储过程代码

/*
数据库分页存储过程，支持倒序和升序
参数说明：
  @tablename：为搜索表名
  @tablefield：为表的字段，约定为表的主键,
  @where：为搜索表名，要显示所有记录请设为"1=1"
  @orderby：为搜索结果排序，如order by id desc
  @fieldlist：为字段列表，如userid, username
  @curpage：当前页码
  @page_record：每页记录条数
  @Sort：排序标识（如果是倒序排，参数值为desc，为升序，参数值为asc，跟orderby参数是对应的）
结果：	返回表tablename中满足条件where的第curpage页的page_record条记录，结果按orderby排序
*/
CREATE PROCEDURE proc_CommonPaging

@tablename varchar(100),
@TableField varchar(20),
@Where varchar(5000),
@Orderby varchar(500),
@FieldList varchar(1000),
@CurPage int,
@Page_Record int,
@Sort varchar(8)

AS

BEGIN

  DECLARE @Cmd varchar(8000)
  DECLARE @UpRecord int
  DECLARE @Op varchar(2) -- 操作符
  DECLARE @Max_Min varchar(4) -- 最大/最小计算

  SET @Op = '<'
  SET @Max_Min = 'MIN'
  IF @Sort = 'asc'
		BEGIN
	  SET @Op = '>'
					SET @Max_Min = 'MAX'
		END

  SET @UpRecord=@CurPage * @Page_Record

  IF @CurPage = 0
			SET @Cmd = 'SELECT TOP '+cast(@Page_Record AS NVARCHAR)+' '+@FieldList+' FROM '+@TableName+' WHERE '+@Where+' '+@Orderby
  ELSE
	  SET @Cmd = 'SELECT TOP '+cast(@Page_Record AS NVARCHAR)+' '+@FieldList+' FROM '+@TableName+' WHERE '+@Where+' AND '+@TableField+'
  	'+@Op+' (SELECT '+@Max_Min+'('+@TableField+')  
	FROM (SELECT TOP  '+cast(@UpRecord AS NVARCHAR)+' '+@TableField+' FROM '+@TableName+' WHERE  
	'+@Where+' '+@Orderby+') AS TmpTbl ) AND '+@Where+' '+@Orderby

  SET @Cmd = @Cmd + '; SELECT COUNT(*) FROM '+@TableName+' WHERE '+@Where

  EXEC(@Cmd)
  PRINT(@Cmd)

END
GO

完成判断

成功创建后，调用示例如下：

-- 查询 Users 表第 1 页（索引为0），每页 10 条，按 ID 升序排列
EXEC proc_CommonPaging
    @Tablename = 'Users',
    @TableField = 'UserID',
    @Where = 'Status = 1',
    @Orderby = 'ORDER BY UserID ASC',
    @FieldList = '*',
    @CurPage = 0,
    @Page_Record = 10,
    @Sort = 'asc'

执行后，结果集会包含两部分：第一部分为分页数据，第二部分为符合条件（如 Status = 1）的总记录数。

简要总结

以上是对 SQL Server 通用分页存储过程的详细内容，更多关于数据库查询优化的资料请关注本站其它相关文章！