sql2005 存储过程分页代码

create database test
on primary ( name='test_data.mdf',
filename='d:\我的资料\sql\备份\test_data.mdf'
)
log on
(
name='test_data.ldf',
filename='d:\我的资料\sql\备份\test_data.ldf'
)

if object_id('tb') is not null drop table tb
create table tb
(
col int
)
insert into tb select top 50 number from master..spt_values where type='p' and number>0

create proc splitpage
(
@tablename nvarchar(50),
@pagesize int,--每页显示的数量
@currentpage int,--当前第几页
@pagecol nvarchar(50),--排序字段
@orderno nvarchar(50)--排序方式(desc,asc)
)
as
/*
测试用的
declare @pagecol nvarchar(50)
declare @tablename nvarchar(50)
declare @orderno nvarchar(50)
declare @pagesize int
declare @currentpage int
set @pagecol='col'
set @tablename='tb'
set @orderno='desc'
set @pagesize=10
set @currentpage=4
*/
declare @sql nvarchar(1000)
set @sql=''
set @sql='
;with hgo as
(
select *,row_number() over(
order by '+@pagecol+' '+@orderno+') rank
from '+@tablename+'
)'
set @sql=@sql+'select col from hgo where rank between '+ltrim((@currentpage-1)*@pagesize+1)+' and '+ltrim(@currentpage*@pagesize)
--print @sql
exec (@sql)

exec splitpage 'tb',10,1,'col','desc'

col
-----------
50
49
48
47
46
45
44
43
42
41

(10 行受影响)

exec splitpage 'tb',10,3,'col','desc'
col
-----------
30
29
28
27
26
25
24
23
22
21

(10 行受影响)