如何实现SQL Server分页存储过程与MuleSoft的对接？

核心问题修正 #

你当前的存储过程用WHILE循环一次性返回所有分页的结果集，这对MuleSoft来说会增加数据处理复杂度——MuleSoft通常期望单次请求返回单页数据+分页元数据，而非所有页的批量结果。

调整后的存储过程设计 #

我们需要将存储过程改为接受输入参数（当前页码、每页行数），同时返回当前页数据，以及前端分页所需的元数据（总记录数、总页数）。不需要返回OFFSET值，因为它可以通过输入参数计算得出，无需暴露给调用方。

修改后的存储过程代码

CREATE OR ALTER PROCEDURE pldScratch.GetFruitsPage
    @PageNumber INT,
    @RowsOfPage INT,
    @TotalRecords INT OUTPUT,
    @TotalPages INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    -- 计算总记录数和总页数
    SELECT @TotalRecords = COUNT(*) FROM pldScratch.SampleFruits;
    SET @TotalPages = CEILING(CAST(@TotalRecords AS DECIMAL(10,2)) / @RowsOfPage);

    -- 边界校验：如果请求页码超出范围，返回空结果
    IF @PageNumber < 1 OR @PageNumber > @TotalPages
    BEGIN
        SELECT FruitName, Price FROM pldScratch.SampleFruits WHERE 1=0;
        RETURN;
    END

    -- 返回当前页数据
    SELECT FruitName, Price 
    FROM pldScratch.SampleFruits
    ORDER BY Price 
        OFFSET (@PageNumber - 1) * @RowsOfPage ROWS
        FETCH NEXT @RowsOfPage ROWS ONLY;
END
GO

给MuleSoft的返回内容说明 #

MuleSoft调用这个存储过程时，需要：

• 传入两个输入参数：@PageNumber（当前要请求的页码）、@RowsOfPage（每页行数）
• 接收两个输出参数：@TotalRecords（总记录数）、@TotalPages（总页数）
• 接收当前页的水果数据结果集

这些信息足以支持前端实现完整的分页功能：

• 前端可以用@TotalPages判断是否有下一页/上一页
• 用@TotalRecords展示总数据量
• 用当前页的结果集渲染列表

调用示例（SQL端测试） #

DECLARE @TotalRecords INT, @TotalPages INT;
EXEC pldScratch.GetFruitsPage 
    @PageNumber = 2, 
    @RowsOfPage = 4,
    @TotalRecords = @TotalRecords OUTPUT,
    @TotalPages = @TotalPages OUTPUT;

PRINT '总记录数：' + CAST(@TotalRecords AS VARCHAR);
PRINT '总页数：' + CAST(@TotalPages AS VARCHAR);

额外建议 #

1. 给ORDER BY字段添加索引：因为分页依赖Price排序，创建索引CREATE NONCLUSTERED INDEX IX_SampleFruits_Price ON pldScratch.SampleFruits(Price);可以大幅提升大表的分页性能。
2. 输入参数校验：存储过程里已经加了页码范围校验，避免无效请求返回错误数据。

内容的提问来源于stack exchange，提问作者plditallo