SQL pagination example
When we are bind the grid with pagination should we need to control both side (client side and server side).For example if we have 100 rows and need to display 20 rows per page, so we should calculate the pagination from total count and limit, total count will get from procedure. Like below
select * from( select row_number() over(order by RegisterNumber desc) as RowNum,* from( select COUNT(*) over() as Totalcount,EmployeeName,RegisterNumber from clg_tb_student )x)xx where xx.RowNum>@startIndex and xx.RowNum<=@endIndex
In the above procedure, If the @startIndex is 0 and @endIndex is 10 then the procedure will return only 10 records and the count column field will tell you the total count of your records.
Easy method to applying your existing procedure
consider the below is your existing procedure
select * from .............
Remove the select and just copy and paste the following code to above of your existing code.
select * from( select row_number() over(order by RegisterNumber desc) as RowNum,* from( select COUNT(*) over() as count,
select * from( select row_number() over(order by RegisterNumber desc) as RowNum,* from( select COUNT(*) over() as count, * from .............
Copy and paste the following code to below of the modified code
)x)xx where xx.RowNum>@startIndex and xx.RowNum<=@endIndex
select * from( select row_number() over(order by RegisterNumber desc) as RowNum,* from( select COUNT(*) over() as count, * from ............. )x)xx where xx.RowNum>@startIndex and xx.RowNum<=@endIndex
0 comments:
Post a Comment