alter procedure proc_Page
@Table varchar(50), --表名
@PKey varchar(50), --主键
@Condition varchar(500), --查询条件
@PageNumber int, --开始页数
@PageSize int, --每页大小
@CountPage int output --总页数输出
as
if len(LTrim(RTrim(@Condition)))=0 Set @Condition='1=1' --如果未输入条件默认没有条件
declare @Sql nvarchar(4000)
declare @Counts int,@c int --总行数,@c sql语句中的变量输出
set @Sql=N'select @C=Count('+@PKey+') from '+@Table+' where '+@Condition
exec sp_executesql @Sql,N'@C int output',@c output
set @Counts=@c
set @CountPage=ceiling(@Counts*1.0/@PageSize)
if @PageNumber>@CountPage set @PageNumber=@CountPage --如果请求页大于最大页数,请求也是最大页
if @PageNumber<=0 set @PageNumber=1 --请求也小于1,请求是1
if @PageNumber>1
begin
set @sql=N'select top '+cast(@PageSize as varchar(10))+' * from '+@Table+' '+
'where id> (select max(id) from '+
'(select top '+cast(@PageNumber*@PageSize-@PageSize as varchar(10))+' id from '+@Table+' order by '+@Pkey+') as a) '+
'order by '+@Pkey
end else
begin
set @Sql=N'select top '+cast(@PageSize as varchar(10))+' * from '+@Table+' order by '+@Pkey
end
exec (@Sql)
Go
--测试
--declare @out int
--exec proc_Page @Table='test',@Pkey='id',@Condition='',@pageNumber=4,@PageSize=2,@CountPage=@out output
--select @Out
不用Row_Number实现