Stored Procedure (SP) pagination for a data grid

Implementing Stored Procedure (SP) pagination for a data grid involves retrieving only a specific "page" of data from the database, rather than the entire dataset, which significantly improves performance and reduces network traffic. The modern and most efficient method in SQL Server (2012 and later) is using the OFFSET...FETCH NEXT clause. 

Stored Procedure Implementation (SQL Server)

The stored procedure needs input parameters for the page number, page size, and an ORDER BY clause (which is mandatory for OFFSET...FETCH). It may also include an output parameter for the total record count, which is useful for displaying navigation controls on the front end. 

sql

CREATE OR ALTER PROCEDURE [dbo].[uspGetPagedData]
    @PageNumber INT,
    @PageSize INT,
    @TotalRecords INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    -- Get the total number of records
    SELECT @TotalRecords = COUNT(*) FROM [YourTableName];

    -- Retrieve the specific page of data
    SELECT *
    FROM [YourTableName]
    ORDER BY [YourSortColumn] ASC -- ORDER BY is required for OFFSET/FETCH
    OFFSET (@PageNumber - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;
END

• @PageNumber, @PageSize: Input parameters to determine which data to retrieve.
• @TotalRecords: An output parameter to return the total count of records in the table, enabling the UI to calculate the total number of pages.
• OFFSET (@PageNumber - 1) * @PageSize ROWS: Skips the appropriate number of rows based on the current page and size.
• FETCH NEXT @PageSize ROWS ONLY: Selects only the number of rows specified by the page size.