存储过程分页

create proc PageShow
(
@where nvarchar(max),
@pageindex int,
@pagesize int,
@totalpage int output,
@totalNum int output
)
as
begin
declare @totalsql nvarchar(max)
set @totalsql=‘select @totalNum=COUNT() from(select ROW_NUMBER() over(order by CId) num , from Client where 1=1 ‘+@where+’)t’
exec sp_executesql @totalsql,N’@totalNum int output’,@totalNum output
set @totalpage=CEILING(@totalNum1.0/@pagesize)
declare @sql nvarchar(max),@startrow int,@endrow int
set @startrow=(@pageindex-1)@pagesize+1
set @endrow=@pageindex*@pagesize
set @sql=‘select * from(select ROW_NUMBER() over(order by CId) num ,* from Client where 1=1’+@where+’)t where num between ‘+CAST(@startrow as nvarchar(10))+’ and '+CAST(@endrow as nvarchar(10))
exec (@sql)
end
go
declare @totalpage int ,@totalNum int
exec PageShow ‘’,1,3,@totalpage output,@totalNum output
select @totalpage,@totalNum
select * from(select ROW_NUMBER() over(order by CId) num ,* from Client where 1=1)t where num between 1and 3