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

Question

How to use Pagination in stored procedure without using OFFSET MSSQL server?
I want to implement paging Like Google in a asp .net web application (MVC)

ALTER PROCEDURE BooksGetList
    @BookName VARCHAR(50) = null, @BookPublisherId INT = null, @BookCategoryId INT = null
    AS BEGIN
    SELECT 
        ISNULL([Books].[BookId], '') AS [BookId],
        ISNULL([Books].[BookName], '') AS [BookName],
        ISNULL([BookCategories].[BookCategoryId], '') AS [BookCategoryId],
        ISNULL([BookCategories].[BookCategoryName], '') AS [BookCategoryName],
        ISNULL([BookPublishers].[BookPublisherId], '') AS [BookPublisherId],    
        ISNULL([BookPublishers].[BookPublisherName], '') AS [BookPublisherName],    
        ISNULL([Books].[BookQuantity], '') AS [BookQuantity],
        ISNULL([Books].[IsActive], '') AS [IsActive]
    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)

    
    ORDER BY BookId

END
GO

AS you are askingfor a stored procedure, better include SQL related tags — J.Salas, Mar 02 '22 at 14:26
Get rid of all those ISNULL functions. If they are needed, then that suggests you have a seriously flawed schema. Surely BookId is the primary key of Books - so how could it be NULL? How could a book have a NULL value for BookName? You join on BookCategoryId - it cannot possibly be null. — SMor, Mar 02 '22 at 14:38
https://stackoverflow.com/questions/2135418/equivalent-of-limit-and-offset-for-sql-server — Robin Webb, Mar 02 '22 at 14:44
Do you want perhaps Keyset Pagination, see [Is there any better option to apply pagination without applying OFFSET in SQL Server?](https://stackoverflow.com/questions/70519518/is-there-any-better-option-to-apply-pagination-without-applying-offset-in-sql-se) — Charlieface, Mar 02 '22 at 14:57

score 0 · Answer 1 · answered Mar 25 '22 at 06:10

If you use 2008 R2 or older you can't use OFFSET FETCH,

you have alternative to use ROW_NUMBER() and rewrite your query for example:

with OFFSET

SELECT Price
FROM dbo.Inventory
ORDER BY Price OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY

this query without OFFSET using ROW_NUMBER()

SELECT Price
FROM
(
SELECT Price
ROW_NUMBER() OVER (ORDER BY Price) AS Seq
FROM dbo.Inventory
)t
WHERE Seq BETWEEN 11 AND 15

score 0 · Accepted Answer · answered Mar 30 '22 at 07:57

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

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

2 Answers2