Pagination with The Stored Procedure

Pagination with the stored procedure

One way (possibly not the best) to do it is to use dynamic SQL

CREATE PROCEDURE [sp_Mk]
 @page INT,
 @size INT,
 @sort nvarchar(50) ,
 @totalrow INT  OUTPUT
AS
BEGIN
    DECLARE @offset INT
    DECLARE @newsize INT
    DECLARE @sql NVARCHAR(MAX)

    IF(@page=0)
      BEGIN
        SET @offset = @page
        SET @newsize = @size
       END
    ELSE 
      BEGIN
        SET @offset = @page*@size
        SET @newsize = @size-1
      END
    SET NOCOUNT ON
    SET @sql = '
     WITH OrderedSet AS
    (
      SELECT *, ROW_NUMBER() OVER (ORDER BY ' + @sort + ') AS ''Index''
      FROM [dbo].[Mk] 
    )
   SELECT * FROM OrderedSet WHERE [Index] BETWEEN ' + CONVERT(NVARCHAR(12), @offset) + ' AND ' + CONVERT(NVARCHAR(12), (@offset + @newsize)) 
   EXECUTE (@sql)
   SET @totalrow = (SELECT COUNT(*) FROM [Mk])
END

Here is SQLFiddle demo

Parameterized stored procedure paginated return row count based on parameters excluding pagesize parameter

A couple of options:

1. Duplicate the query without the TOP just to get the count(*)

2. Use your query as it stands to get the id and insert that into a temp table which contains an identity column (RowNum) which you then use for paging then obtain the count before obtaining the actual records e.g.

    create table #temp (id int, RowNum int identity(1,1));
    create index #IK_temp on #temp (RowNum);

    -- insert your ids from your full query here

    -- Assign your total record count
    select @TotalRecords = count(*) from #temp; -- To get the total record count

    -- Return the paged records
    select *
    from MyTable
    where id in (
      select id
      from #temp
      order by RowNum asc
      offset (@PageStart-1) rows fetch next @Pagesize rows only
    );

Option 2 will perform significantly better because it reduces the amount of data that SQL Server has to deal with.

As an aside you should never select * as it leads to unexpected, and potentially badly performing behaviour. Always list your columns.

How do you implement pagination with a result set from a stored procedure?

You can convert your List to a Page by using the PageImpl constructor(s). If you want an unpaged Page all you have to do is the following:

Page<SearchResult> searchResultsPage = new PageImpl<>(searchResults);

If you want an actual paged result you'll also need a pageable and total records count. The conversion would then look like this:

Page<SearchResult> searchResultsPage = new PageImpl<>(searchResults, pageable, count);

A pageable can be created by using PageRequest:

var pageable = PageRequest.of(pageNumber, pageSize, sort); 

How to use Pagination in stored procedure without using OFFSET MSSQL server?

To solve my Problem i used

ALTER PROCEDURE BooksGetList
    @BookName VARCHAR(50) = null, @BookPublisherId INT = null, @BookCategoryId INT = null, @PageNumber INT = 1, @PageSize INT = 10,@TotalRecords INT = null OUT
    AS BEGIN
    SELECT 
        [Books].[BookId],                           [Books].[BookName],
        [BookCategories].[BookCategoryId],          [BookCategories].[BookCategoryName],
        [BookPublishers].[BookPublisherId],         [BookPublishers].[BookPublisherName],   
        [BookQuantity],                             [Books].[IsActive],
        [Books].[CreatedBy],                        [Books].[CreatedOn],        
        [Books].[ModifiedBy],
        [Books].[ModifiedOn],                       ROW_NUMBER() OVER (ORDER BY BookId) as RowNumber into #TempBooks
    FROM
        [Books] Inner Join BookCategories On [BookCategories].BookCategoryId = [Books].BookCategoryId
                Inner Join BookPublishers On [BookPublishers].BookPublisherId = [Books].BookPublisherId
    Where
         ([Books].[BookName] LIKE '%'+@BookName+'%' OR @BookName IS NULL)
         AND  ([BookCategories].BookCategoryId = @BookCategoryId OR @BookCategoryId IS NULL)    
         AND  ([BookPublishers].BookPublisherId  = @BookPublisherId OR @BookPublisherId IS NULL) 
         AND  Books.IsActive = 1 
    ORDER BY 
         BookId 
    SELECT @TotalRecords = COUNT(BookId) FROM #TempBooks    
    SET @TotalRecords =  @TotalRecords
    SELECT *,  @TotalRecords AS TotalRecords FROM #TempBooks
    WHERE RowNumber between (@PageNumber - 1) * @PageSize + 1 and @PageNumber * @PageSize
    DROP TABLE #TempBooks
END
GO

Pagination in a SQL Server stored procedure with duplicated data

You might try using a CTE to isolate the query against the Party table. This would allow you to pull the right number of rows (and the proper total row count) without having to worry about the expansion from the emails and phone numbers.

It would look something like this (rearranging your query above):

DECLARE @CurrentPage int = 1;
DECLARE @PageSize int = 1000;

WITH PartyList AS (
    SELECT 
        COUNT(*) OVER () as TotalCount,
        p.Id AS PartyId         
    FROM 
        [dbo].[Party] AS p WITH(NOLOCK) 
    WHERE 
        p.PartyType = 1 /*Individual*/ 
    GROUP BY -- You might not need this now depending on your data
        p.Id
    ORDER BY 
        p.Id 
        OFFSET (@CurrentPage - 1) * @PageSize ROWS 
        FETCH NEXT @PageSize ROWS ONLY
)
SELECT
    pl.TotalCount,
    pl.PartyId,
    e.EmailAddress,
    pn.PhoneNumber   
FROM PartyList AS pl
    INNER JOIN 
        [dbo].[Email] AS e WITH(NOLOCK) ON pl.[PartyId] = e.[PartyID]
    INNER JOIN 
        [dbo].[PhoneNumber] AS pn WITH(NOLOCK) ON pl.[PartyId] = pn.[PartyID];

Please be aware that the CTE will require the prior statement to end in a semicolon.

---

Related Topics