SQL Server两种分页的存储过程使用介绍

1. 前言

数据库中的分页是Web应用中很常见的需求。实现分页主要有两个步骤：首先查询出一页数据，然后将查询结果呈现给用户。在SQL Server中，我们可以使用存储过程来实现分页功能。本文将介绍SQL Server中两种实现分页的存储过程的使用方法。

2. 常规分页的存储过程

2.1 存储过程代码解析

常规分页的存储过程的代码 non-PK 分页查询代码如下：

CREATE PROCEDURE [dbo].[Paging] 
(   
      @sql            NVARCHAR(MAX), -- 原始 SQL 语句
      @PageIndex      INT = 1, -- 当前页数
      @PageSize       INT = 10, -- 每页显示的记录数量
      @SortFieldName  NVARCHAR(50), -- 排序字段
      @SortOrder      NVARCHAR(4) = 'ASC', -- 排序方式,默认降序
      @RecordCount    INT OUTPUT -- 总记录数
)   
AS  
BEGIN  
      DECLARE @startIndex INT  
      DECLARE @endIndex INT  
      SET @startIndex = (@PageIndex - 1) * @PageSize + 1  
      SET @endIndex = @PageIndex * @PageSize + 1  
      SET @sql = 'WITH r AS (SELECT *, ROW_NUMBER() OVER (ORDER BY ' + @SortFieldName + ' ' + @SortOrder +') AS RowNum FROM (' + @sql + ') AS t) SELECT * FROM r WHERE RowNum >= ' + CAST(@startIndex AS NVARCHAR(10)) + ' AND RowNum < ' + CAST(@endIndex AS NVARCHAR(10))  
      EXEC sp_executesql @sql  
      -- 总记录数  
      SET @RecordCount = @@ROWCOUNT  
END

代码解析：

CREATE PROCEDURE

：定义存储过程。

@sql NVARCHAR(MAX)

：存储过程的输入参数，表示需要分页的 SQL 语句。

@PageIndex INT = 1

：存储过程的输入参数，表示当前页数，默认为 1。

@PageSize INT = 10

：存储过程的输入参数，表示每页显示的记录数量，默认为 10。

@SortFieldName NVARCHAR(50)

：存储过程的输入参数，表示排序字段名称。

@SortOrder NVARCHAR(4) = 'ASC'

：存储过程的输入参数，表示排序方式，默认升序。

@RecordCount INT OUTPUT

：存储过程的输出参数，表示总记录数。

DECLARE @startIndex INT

：定义起始索引。

DECLARE @endIndex INT

：定义结束索引。

SET @startIndex = (@PageIndex - 1) * @PageSize + 1

：计算起始索引。

SET @endIndex = @PageIndex * @PageSize + 1

：计算结束索引。

SET @sql = 'WITH r AS (SELECT *, ROW_NUMBER() OVER (ORDER BY ' + @SortFieldName + ' ' + @SortOrder +') AS RowNum FROM (' + @sql + ') AS t) SELECT * FROM r WHERE RowNum >= ' + CAST(@startIndex AS NVARCHAR(10)) + ' AND RowNum < ' + CAST(@endIndex AS NVARCHAR(10))

：使用动态 SQL 来实现分页查询。

EXEC sp_executesql @sql

：执行查询语句。

SET @RecordCount = @@ROWCOUNT

：记录总记录数。

2.2 存储过程的使用方法

通过上述代码中的存储过程可以实现常规分页功能。使用步骤如下：

将需要分页的 SQL 语句作为输入参数传递给存储过程。

指定当前页数、每页显示的记录数量、排序字段名称和排序方式。

执行存储过程，得到查询结果和总记录数。

下面是一个使用示例：

DECLARE @RecordCount INT  
EXEC dbo.Paging 
     @sql = N'SELECT * FROM Persons', 
     @PageIndex = 2, 
     @PageSize = 10, 
     @SortFieldName = 'PersonID', 
     @SortOrder = 'ASC', 
     @RecordCount = @RecordCount OUTPUT  
SELECT @RecordCount

3. 快速分页的存储过程

3.1 存储过程代码解析

快速分页的存储过程的代码如下：

CREATE PROCEDURE [dbo].[QuickPaging] 
(
    @TableName  VARCHAR(255), -- 必选，表名
    @PageIndex  INT = 1,      -- 当前页数
    @PageSize   INT = 10,     -- 每页显示的记录数量
    @OrderBy    VARCHAR(200), -- 排序字段
    @OrderType  VARCHAR(4) = 'ASC', -- 排序方式
    @TotalCount INT OUTPUT    -- 输出总记录数
)   
AS   
BEGIN
    /*
        计算起始位置和结束位置
        ( 10 -1) * 20 + 1 = 181
    */
    DECLARE @Start INT, @End INT
    SET @Start = (@PageIndex - 1) * @PageSize + 1
    SET @End = (@PageIndex) * @PageSize
    --返回总记录数
    DECLARE @sql NVARCHAR(1000)
    SET @sql = 'SELECT @TotalCount = COUNT(*) FROM [' + @TableName +'] WITH(NOLOCK)'
    EXECUTE sp_executesql @sql, N'@TotalCount INT OUTPUT', @TotalCount = @TotalCount OUTPUT
    --如果总记录数为 0，直接返回
    IF @TotalCount = 0
        RETURN 
    DECLARE @Select NVARCHAR(1000)
    SET @Select = 
        'SELECT TOP ' + CAST(@PageSize AS VARCHAR(100)) + 
        ' * FROM [' + @TableName + '] WITH(NOLOCK) WHERE [' + @OrderBy + 
        '] ' + @OrderType + ' NOT IN (SELECT TOP ' + CAST(@Start-1 AS VARCHAR(100)) + 
        ' [' + @OrderBy + '] FROM [' + @TableName + '] WITH(NOLOCK) ORDER BY ' + 
        @OrderBy + ' ' + @OrderType + ')' +
        ' ORDER BY [' + @OrderBy + '] ' + @OrderType
    EXECUTE sp_executesql @Select
END

代码解析：

CREATE PROCEDURE

：定义存储过程。

@TableName VARCHAR(255)

：存储过程的输入参数，表示需要分页的表名。

@PageIndex INT = 1

：存储过程的输入参数，表示当前页数，默认为 1。

@PageSize INT = 10

：存储过程的输入参数，表示每页显示的记录数量，默认为 10。

@OrderBy VARCHAR(200)

：存储过程的输入参数，表示排序字段。

@OrderType VARCHAR(4) = 'ASC'

：存储过程的输入参数，表示排序方式，默认升序。

@TotalCount INT OUTPUT

：存储过程的输出参数，表示总记录数。

DECLARE @Start INT, @End INT

：定义起始位置和结束位置。

SET @Start = (@PageIndex - 1) * @PageSize + 1

：计算起始位置。

SET @End = (@PageIndex) * @PageSize

：计算结束位置。

SET @sql = 'SELECT @TotalCount = COUNT(*) FROM [' + @TableName +'] WITH(NOLOCK)'

：动态查询总记录数。

EXECUTE sp_executesql @sql, N'@TotalCount INT OUTPUT', @TotalCount = @TotalCount OUTPUT

：执行查询语句，得到总记录数。

IF @TotalCount = 0 RETURN

：如果总记录数为 0，直接返回。

SET @Select = 'SELECT TOP ' + CAST(@PageSize AS VARCHAR(100)) + ' * FROM [' + @TableName + '] WITH(NOLOCK) WHERE [' + @OrderBy + '] ' + @OrderType + ' NOT IN (SELECT TOP ' + CAST(@Start-1 AS VARCHAR(100)) + ' [' + @OrderBy + '] FROM [' + @TableName + '] WITH(NOLOCK) ORDER BY ' + @OrderBy + ' ' + @OrderType + ')' + ' ORDER BY [' + @OrderBy + '] ' + @OrderType

：使用动态 SQL 来实现分页查询。

EXECUTE sp_executesql @Select

：执行查询语句，得到查询结果。

3.2 存储过程的使用方法

使用快速分页的存储过程也非常简单。使用步骤如下：

将需要分页的表名作为输入参数传递给存储过程。

指定当前页数、每页显示的记录数量、排序字段名称和排序方式。

执行存储过程，得到查询结果和总记录数。

下面是一个使用示例：

DECLARE @TotalCount INT
EXECUTE [dbo].[QuickPaging] @TableName = 'Person', @PageIndex = 1, @PageSize = 15, @OrderBy = 'PersonID', @OrderType = 'ASC', @TotalCount = @TotalCount OUTPUT
SELECT @TotalCount

4. 总结

通过上述介绍，我们可以看出快速分页和常规分页两种存储过程在实现分页功能上并无本质区别，但相信读者在一定的应用场景下，可以根据自己的需求选择相应的存储过程。在实际应用中，我们应该根据查询量的大小和性能要求来选择适当的存储过程，以便提高查询效率和数据访问速度。