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