How to Create Server-Side Pagination, Searching, and Sorting Stored Procedure

Introduction

Handling large datasets efficiently is crucial for web applications. Server-side pagination, searching, and sorting help reduce the amount of data sent to the client and improve performance. In this article, we'll demonstrate how to create a stored procedure in SQL Server to achieve this.

ALTER PROCEDURE [dbo].[UserDetails_GetAll]
    @rowCount INT,
    @pageNo INT,
    @sortColumn NVARCHAR(128) = 'UserID',
    @sortDirection VARCHAR(4) = 'DESC',
    @searchText VARCHAR(100) = NULL
AS
BEGIN
    DECLARE @TotalRowCount INT = 0;
    DECLARE @LSearchText VARCHAR(100);    
    SET @LSearchText = ISNULL(@searchText, '');   
    -- Calculate the total number of rows
    SELECT @TotalRowCount = COUNT(1)
    FROM [dbo].[UserDetails] US
    WHERE (
        @LSearchText = ''
        OR US.[FirstName] LIKE '%' + @LSearchText + '%'
        OR US.[LastName] LIKE '%' + @LSearchText + '%'
    )   
    -- Return the total row count
    SELECT @TotalRowCount AS TotalRows    
    -- Fetch the paginated, sorted, and filtered data
    SELECT
        CONVERT(VARCHAR(50), US.[UserID]) AS UserID,
        CONVERT(VARCHAR(50), US.[ClientID]) AS ClientID,
        A.UserName,
        A.Email,
        US.FirstName,
        US.LastName,
        US.ContactNumber,
        FS.FileAppPath AS ProfileImagePath
    FROM
        [dbo].[UserDetails] AS US
    LEFT JOIN
        AspNetUsers AS A ON A.Id = US.UserID
    LEFT JOIN
        [dbo].[FileStore] FS ON FS.FileUID = US.ProfileImageFileUID
    WHERE
        @LSearchText = ''
        OR US.[FirstName] LIKE '%' + @LSearchText + '%'
        OR US.[LastName] LIKE '%' + @LSearchText + '%'
    ORDER BY
        CASE WHEN @sortColumn = 'UserName' AND @sortDirection = 'ASC' THEN A.[UserName] END ASC,
        CASE WHEN @sortColumn = 'UserName' AND @sortDirection = 'DESC' THEN A.[UserName] END DESC,
        CASE WHEN @sortColumn = 'FirstName' AND @sortDirection = 'ASC' THEN US.[FirstName] END ASC,
        CASE WHEN @sortColumn = 'FirstName' AND @sortDirection = 'DESC' THEN US.[FirstName] END DESC,
        CASE WHEN @sortColumn = 'LastName' AND @sortDirection = 'ASC' THEN US.[LastName] END ASC,
        CASE WHEN @sortColumn = 'LastName' AND @sortDirection = 'DESC' THEN US.[LastName] END DESC,
        CASE WHEN @sortColumn = 'ContactNumber' AND @sortDirection = 'ASC' THEN US.[ContactNumber] END ASC,
        CASE WHEN @sortColumn = 'ContactNumber' AND @sortDirection = 'DESC' THEN US.[ContactNumber] END DESC,
        CASE WHEN @sortColumn = 'Email' AND @sortDirection = 'ASC' THEN A.[Email] END ASC,
        CASE WHEN @sortColumn = 'Email' AND @sortDirection = 'DESC' THEN A.[Email] END DESC
    OFFSET ((@pageNo - 1) * @rowCount) ROWS
    FETCH NEXT @rowCount ROWS ONLY
END

Explanation

1. Parameters
   • @rowCount: Number of rows per page.
   • @pageNo: Current page number.
   • @sortColumn: Column name to sort by.
   • @sortDirection: Sorting direction (ASC or DESC).
   • @searchText: Text to search in FirstName and LastName.
2. Total Row Count: Calculate the total number of rows matching the search criteria using COUNT(1).
3. Data Retrieval
   • Join UserDetails with AspNetUsers and FileStore to get the required data.
   • Use ORDER BY with conditional sorting based on the parameters.
   • Use OFFSET and FETCH NEXT for pagination.

Conclusion

In this article, we have created a stored procedure that handles server-side pagination, searching, and sorting. This approach ensures that only the required data is fetched from the server, improving performance and reducing load times. By following these steps, you can efficiently manage large datasets in your applications.