Pagination is common requirement for any application,
where we pull data in chunks depending upon the page number and size of page.
Here in this article we will see how we can achieve pagination through SQL
server prior to SQ: Server 2012,
Here we are declaring a table Employee with three fields.
create table Employees (
empid int NOT NULL,
empname varchar(25) NOT NULL,
salary decimal(15,2)
)
Command(s)
completed successfully.
Lets insert few data in the table.
declare @i int = 0
while @i < 10000
begin
insert into Employees
select round(rand() * 10000,0),'abc' + cast(round(rand()*10,0) as varchar), rand()*1000
set @i = @i + 1
end
Now since our table is ready, its time to enter
implement pagination, here in the below script, we have taken 2 variable, @PageNo,
which indicate which page to see for data and second one is @Size which says
the number of records in each page. Here we are using Row_Number ranking
function, Approach is
- · We are giving a row number in sequence to all rows of table using ROW_NUMBER Rank function.
- · Then according to the value of page number and page size, we are extracting the records.
- · Eg. If @PageNo is 4 and row @PageSize is 5 then the row to return should have 20-25 as row numbers.
declare
@pageNo int
set
@pageNo=1
declare
@Size int
set
@Size=5
select * from (select ROW_NUMBER () over ( order by empid asc ) as rowno , employees.* from Employees ) s
where s.rowno > (@pageNo*@Size) and s.rowno <=((@pageNo+1)*@Size)
rowno empid empname salary
--------------------
----------- ------------------------- ---------
1 10 abc5 80.35
2 11 abc9 287.90
3 12 abc5 815.79
4 13 abc5 433.73
5 14 abc4 645.19
That’s all one need to do to implement Pagination in
SQL Server Prior to SQL Server 2012.
Everything gets better when you have
tea and SQL Server J
Post Reference: Vikram Aristocratic Elfin Share
No comments:
Post a Comment