SQL Server 分页查询通用存储过程(只做分页查询用)

1. SQL Server 分页查询通用存储过程

在实际开发中，经常会遇到需要分页查询的情况。而 SQL Server 没有提供原生的分页查询功能，需要通过一些技巧来实现。为了方便使用，我们一般会封装一个通用的存储过程，在需要进行分页查询的时候直接调用即可。

2. 存储过程参数

通用的分页查询存储过程需要以下参数：

2.1 @TableName（表名）

用于指定要进行分页查询的数据表名称。

2.2 @Fields（字段列表）

用于指定要查询的字段列表，多个字段之间用逗号分隔。

2.3 @OrderField（排序字段）

用于指定排序的字段名。

2.4 @PrimaryKey（主键名）

用于指定数据表的主键名。

2.5 @PageSize（每页记录数）

用于指定每页显示的记录数量。

2.6 @PageIndex（当前页数）

用于指定当前要显示的页数。

2.7 @TotalRecordCount（总记录数）

用于返回满足条件的记录总数。

3. 分页查询实现

实现分页查询的核心代码如下所示：

DECLARE @startRowIndex int
DECLARE @endRowIndex int
-- 计算起始记录索引和结束记录索引
SET @startRowIndex = (@PageIndex - 1) * @PageSize
SET @endRowIndex = @startRowIndex + @PageSize + 1
-- 构造 SQL 语句
DECLARE @sql nvarchar(max)
SET @sql = N'SELECT * FROM 
    (SELECT ROW_NUMBER() OVER (ORDER BY '+ @OrderField +' ASC) AS RowNumber, ' + @FieldName + '
    FROM '+ @TableName +') AS T 
WHERE T.RowNumber > ' + CONVERT(varchar(10), @startRowIndex) + 
    ' AND T.RowNumber < ' + CONVERT(varchar(10), @endRowIndex)
-- 返回结果集和总记录数
SELECT * FROM (' + @sql + ') AS List 
WHERE List.RowNumber BETWEEN ' + CONVERT(varchar(10), @startRowIndex + 1) + 
    ' AND ' + CONVERT(varchar(10), @endRowIndex - 1)
SELECT @TotalRecordCount = COUNT(*) FROM '+ @TableName +'

上述代码首先计算出当前页要显示的记录范围，然后使用 ROW_NUMBER() 函数给结果集中的每行记录添加一个序号，最后根据序号范围返回当前页的结果集和总记录数。

4. 使用示例

使用分页查询通用存储过程的示例代码如下所示：

DECLARE @TotalRecordCount int
EXEC PagingQuery
    @TableName = 'Products',
    @Fields = 'ProductID, ProductName, CategoryID, UnitPrice',
    @OrderField = 'ProductID',
    @PrimaryKey = 'ProductID',
    @PageSize = 10,
    @PageIndex = 1,
    @TotalRecordCount = @TotalRecordCount OUTPUT
SELECT @TotalRecordCount TotalRecordCount

上述代码指定了要查询的数据表名称、字段列表、排序字段名、主键名、每页显示的记录数和当前页数，最后将总记录数输出到变量 TotalRecordCount 中。

5. 总结

通过封装一个通用的分页查询存储过程，我们可以方便地在需要进行分页查询的场景下直接调用，无需重复编写分页查询代码。同时，使用此方法也可以提高程序的性能和可维护性。