Saturday, January 23, 2016

Pagination with OFFSET / FETCH : A better way

ALTER PROCEDURE [dbo].[sproc_GetBookList]
     @BookID int =null
,@BookName nvarchar(200)=''
,@AuthorName nvarchar(200)=''
,@BookClassificationID int= null
,@IsNew bit=null
,@PAGESIZE INT = -1
    ,@PAGENUMBER INT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    Select  Count(*) over () AS TotalRecordsCount,* from Book
   Where
 (
    (Book.BookName LIKE N'%'+@BookName+'%' OR @BookName ='')    and
(Book.AuthorName LIKE N'%'+@AuthorName+'%' OR @AuthorName ='') and
(Book.BookClassificationID =@BookClassificationID OR @BookClassificationID = 0 OR @BookClassificationID is null) and  
(Book.IsNew =@IsNew OR @IsNew = 0 OR @IsNew is null)  and
(Book.ID =@BookID OR @BookID = 0 OR @BookID is null)  
 )
ORDER BY Book.BookName ASC OFFSET @PAGESIZE * (@PAGENUMBER - 1) ROWS
    FETCH NEXT @PAGESIZE ROWS ONLY;




reference:
http://sqlperformance.com/2015/01/t-sql-queries/pagination-with-offset-fetch

No comments:

Post a Comment