Pagination

Pagination is the most important task for developer to develop application.

There is several way we can get result to a query window of sql server.

using @RowNumber and Sub query we can get exact pagination information from the database.

So how to get 11 to 20 rows from the table. you can use as below

Select * from(
select ROW_NUMBER()over(order by FieldId)as RowN, *
from TableName
) as Field
where Field.RowN between 11 and 20;

Now dynamically want to select data from the table using where cause:

To understand paginatin using RowNumber in T-SQL statment

we created two variable to get exact rows.

Declare @Total int = 10, @Page int = 2

Select * from(
select ROW_NUMBER()over(order by FieldId)as RowN, *
from TableName
) as Field
where Field.RowN  >= ((@Page-1)*@Total+1) and Field.RowN <= @Total*@Page;

and using between:

Declare @Total int = 10, @Page int = 2

Select * from(
select ROW_NUMBER()over(order by FieldId)as RowN, *
from TableName
) as Field
where Field.RowN between ((@Page-1)*@Total+1) and @Total*@Page;

To execute procedure

Execute SelectTableRows 20, 2

How to create pagination using stored procedure?

create procedure SelectTableRows
@Total int, 
@Page int
AS
 Select * from(
 select ROW_NUMBER()over(order by FieldId)as RowN, *
 from TableName
 ) as Field
 where Field.RowN between ((@Page-1)*@Total+1) and @Total*@Page;
GO

From SQL Server 2012, we can use OFFSET and FETCH NEXT Clause to achieve the pagination.

very simple way to get pagination using order by with offset

Declare @Total int = 10, @Page int = 2


Select  *
from TableName
order by FieldId
offset ((@Page-1)*@Total) rows fetch next @Total rows only;

Offset is a function.

instead of getting pagination with the help Row_Number() function, offset is quite more fast to get result.

you can also visit:  www.mybook-lang.com