MSSQL分页存储过程完整示例(支持多表分页存储)

MSSQL数据库中的分页存储过程是非常有用的，它允许我们在处理大量数据时，按需返回数据的部分内容。下面是一个基本的MSSQL分页存储过程的示例。

基本分页存储过程

CREATE PROCEDURE [dbo].[spGetRecordsPageWise]
(
    @PageIndex INT = 1,
    @PageSize INT = 10,
    @TotalRecords INT OUTPUT
)
AS
BEGIN
    --声明变量
    DECLARE @PageLowerBound INT, @PageUpperBound INT;

    --计算页边界
    SET @PageLowerBound = (@PageIndex - 1) * @PageSize + 1;
    SET @PageUpperBound = @PageLowerBound + @PageSize - 1;

    --查询总记录数
    SELECT @TotalRecords = COUNT(*) FROM [YourTable];

    -- 查询指定页的数据
    SELECT *
    FROM
    (
        SELECT ROW_NUMBER() OVER(ORDER BY Id ASC) AS RowNum, *
        FROM [YourTable]
    ) AS MyDerivedTable
    WHERE RowNum BETWEEN @PageLowerBound AND @PageUpperBound
    ORDER BY RowNum
END

这个存储过程接收三个参数：

• @PageIndex：当前页的索引，以1为起始。默认值为1。
• @PageSize：每页的记录数。默认值为10。
• @TotalRecords：总记录数。输出参数。

在存储过程中，我们首先声明了两个变量@PageLowerBound和@PageUpperBound，这两个变量用于计算当前页码的上下界。然后我们查询并输出了总记录数，这个查询由参数@TotalRecords输出。最后，我们使用ROW_NUMBER()窗口函数按照指定属性排序，然后根据上下界条件选择特定范围内的数据。结果集按照RowNum字段排序返回。

多表分页存储过程

当应用系统需要从多个数据表中查询数据并进行分页显示时，可以使用多表分页存储过程。下面展示一个示例。

CREATE PROCEDURE [dbo].[spGetRecordsPageWiseMultiTable]
(
    @PageIndex INT = 1,
    @PageSize INT = 10,
    @TotalRecords INT OUTPUT
)
AS
BEGIN
    --声明变量
    DECLARE @PageLowerBound INT, @PageUpperBound INT;
    DECLARE @Offset INT, @FetchNext INT;

    --计算页边界
    SET @PageLowerBound = (@PageIndex - 1) * @PageSize + 1;
    SET @PageUpperBound = @PageLowerBound + @PageSize - 1;

    --定义临时表变量
    DECLARE @TempTable TABLE (RowNum INT IDENTITY(1, 1), YourColumn1 INT, YourColumn2 VARCHAR(100), YourColumn3 DATETIME)

    --将数据插入到临时表中
    INSERT INTO @TempTable
    SELECT YourTable1.PrimaryKey, YourTable2.ColumnName, YourTable3.ColumnName
    FROM YourTable1
    INNER JOIN YourTable2 ON YourTable1.YourTable1ForeignKey = YourTable2.PrimaryKey
    INNER JOIN YourTable3 ON YourTable2.YourTable2ForeignKey = YourTable3.PrimaryKey
    ORDER BY YourTable1.PrimaryKey ASC;

    --查询总记录数
    SELECT @TotalRecords = COUNT(*) FROM @TempTable;

    --查询指定页的数据
    SET @Offset = @PageLowerBound - 1;
    SET @FetchNext = @PageSize;

    SELECT YourColumn1, YourColumn2, YourColumn3
    FROM @TempTable
    ORDER BY RowNum
    OFFSET @Offset ROWS
    FETCH NEXT @FetchNext ROWS ONLY;
END

与基本存储过程类似，多表分页存储过程首先计算当前页号的上下界，然后执行查询并插入结果到一个临时表中。最后，按照RowNum排序并根据请求条件返回所需数据。

需要注意的是，多表分页存储过程使用了SQL Server 2012提供的新特性，即OFFSET...FETCH NEXT。如果你使用的是2005或更早的版本，则需要使用ROW_NUMBER()窗口函数来达到相同的效果。

通过上述两个例子，我们可以看到，MSSQL分页存储过程是非常实用的工具，能够提高应用程序的性能并提高用户体验。