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

Answer 1

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

Answer 2

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