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)