In this article I will explain with an example, how to implement server-side paging (pagination), searching and sorting using Stored Procedure in SQL Server.
 
 

Stored Procedure for Pagination, Searching and Sorting

The following Stored Procedure is used for paging, searching and sorting the records in SQL Server database.
The Stored Procedure uses ROW_NUMBER function of SQL Server.
The ROW_NUMBER function assigns Row Number (Row Index) to each row of the Table in a specific order.
Note: If you don’t have any specific column for ordering records then you can use the Primary Key for ordering the records.
 
The Table fields along with the Row Number (Row Index) field are selected using Common Table Expression (CTE) and the records are filtered to include only those whose SearchText is NULL or whose ContactName contains the SearchText parameter.
The Row Number (Row Index) is then used to implement Paging using the PageIndex and PageSize parameters.
Finally, the ORDER BY clause ensures that sorting is applied according to the specified SortColumn and SortDirection parameters. If SortColumn and SortDirection parameters are NULL, the default sorting by CustomerID is applied.
If SortColumn and SortDirection parameters are NULL, the default sorting by CustomerID and Ascending Order is applied.
CREATE PROCEDURE GetCustomersPagingSearchingSorting
    @PageIndex INT
   ,@PageSize INT
   ,@SortColumn NVARCHAR(50) = NULL
   ,@SortDirection VARCHAR(4) = NULL
   ,@SearchText VARCHAR(30) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    WITH Paging AS
    (
        SELECT ROW_NUMBER() OVER
           (
               ORDER BY [CustomerID] ASC
           ) RowNumber
           ,[CustomerID]
           ,[ContactName]
           ,[City]
           ,[Country]
        FROM [Customers]
        WHERE @SearchText IS NULL OR [ContactName] LIKE '%'+ @SearchText + '%'
    )
 
    SELECT * FROM Paging
    WHERE RowNumber BETWEEN (@PageIndex -1)* @PageSize + 1 AND (((@PageIndex -1)* @PageSize + 1) + @PageSize)- 1
    ORDERBY
        CASE WHEN @SortColumn = 'CustomerID' AND @SortDirection = 'ASC' THEN [CustomerID] END ASC,
        CASE WHEN @SortColumn = 'CustomerID' AND @SortDirection = 'DESC' THEN [CustomerID] END DESC,
        CASE WHEN @SortColumn = 'ContactName' AND @SortDirection = 'ASC' THEN [ContactName] END ASC,
        CASE WHEN @SortColumn = 'ContactName' AND @SortDirection = 'DESC' THEN [ContactName] END DESC,
        CASE WHEN @SortColumn = 'City' AND @SortDirection = 'ASC' THEN [City] END ASC,
        CASE WHEN @SortColumn = 'City' AND @SortDirection = 'DESC' THEN [City] END DESC,
        CASE WHEN @SortColumn = 'Country' AND @SortDirection = 'ASC' THEN [Country] END ASC,
        CASE WHEN @SortColumn = 'Country' AND @SortDirection = 'DESC' THEN [Country] END DESC
END
 
 

Executing the Stored Procedure

You can execute the Stored Procedure using following command.
EXEC GetCustomersPagingSearchingSorting 1, 10, 'CustomerID', 'ASC', 'Mar'
 
 

Screenshot

Create Server-Side Pagination, Searching, and Sorting using Stored Procedure in SQL Server
 
 

Downloads

Download Code