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