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 =...
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
2 Answers
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
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
Chloe Bennett explores the intersection of pop culture, streaming entertainment, digital trends, and contemporary lifestyle. Her weekly commentary reaches thousands of culture enthusiasts.
