存储过程分页

create proc Proc_Page
(
@where nvarchar(max),
@pindex int,
@psize int,
@allcount int output,
@pagecount int output
)
as
begin
declare @totalsql nvarchar(max)
set @totalsql=‘select @allcount=count(1) from Student where 1=1 ‘+@where+’’
exec sp_executesql @totalsql,N’@allcount int output’,@allcount output
set @pagecount=ceiling(@allcount1.0/@psize)
declare @sql nvarchar(max),@start int,@end int
set @start=(@pindex-1)@psize+1
set @end=@pindex*@psize
set @sql=‘select * from(select row_number() over(order by SId)tt,* from Student where 1=1 ‘+@where+’)t where t.tt between ‘+cast(@start as nvarchar(50))+’ and ‘+cast(@end as nvarchar(50))+’’
exec(@sql)

end
go
declare @all int,@pagecount int
exec Proc_Page ‘and SName=’‘张三’’’,1,3,@all output,@pagecount output
select @all,@pagecount