Getting the Rows from DB in page wise using top clause in SQL Server

state_id  state_name
----------  -------------
1            California
2            Albama
3            Alaska
4            Arizona
5            Florida
6            Hawaii
7            Montana
8            Indiana
9            Lowa
10          New York
11          New Mexico
12          Texas
13          Virginia
14          South Carolina
15          Washington



Create the Procedure like

Create Proc GetRecords(@pageNo int=1,@pageSize int)
as begin
select top (@pageSize) state_id,state_name from tbl_State where state_id not in
(select top (@pageSize*(@pageNo-1)) state_id from tbl_State order by state_id)
order by state_id
end


Execution:-

exec GetRecords 1,4        --1 is page number & 4 is page size

state_id  state_name
----------  -------------
1            California
2            Albama
3            Alaska
4            Arizona

(4 row(s) affected)


exec GetRecords 2,4

state_id  state_name
----------  -------------
5            Florida
6            Hawaii
7            Montana
8            Indiana

(4 row(s) affected)


exec GetRecords 3,4

state_id  state_name
----------  -------------
9            Lowa
10          New York
11          New Mexico
12          Texas

(4 row(s) affected)


exec GetRecords 4,4

state_id  state_name
----------  -------------
13          Virginia
14          South Carolina
15          Washington

(3 row(s) affected)

for the same trick with row_number then check this url: http://mpurna.blogspot.in/2012/07/getting-rows-from-db-in-page-wise-using.html

Tag: Using Top clause in SQL Server