Paging Method for self-written SQL stored procedure

 

Create proc page
@ Count int out,
@ Pageindex int,
@ Pagecount int out
As
Select @ COUNT = count (ID) from user1
Set @ pagecount = floor (@ count/10 + 1)
Declare @ sqlstr nvarchar (1000)
If @ pageindex = 0 or @ pagecount <= 1
Begin
Set @ sqlstr = 'select top 10 * From user1 order by id asc'
End
Else
Begin
If @ pageindex = @ PageCount-1
Begin
Set @ sqlstr = 'select top 10 * From user1 where id not in (select top '+ STR (@ pagecount * 10-10) + 'id from user1) order by id asc'
End
Else
Begin
Set @ sqlstr = 'select top 10 * From user1 where id not in (select top '+ STR (@ pageindex * 10) + 'id from user1) order by id asc'
End
End
Exec (@ sqlstr)
Go