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 行受影响)
