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)