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




下载本文