SqlServer 分页存储过程
create proc [dbo].[proc_Opinion_BaseInfo]
@TableName varchar(4000)，
@PkField varchar(100)，
@PageIndex int=1,
@PageSize int=10,
@SqlWhere nvarchar(4000)，
@RowCount bigint output,
@PageCount bigint output
as
if(@SqlWhere="1")
set @SqlWhere = "1=1"
declare @sql nvarchar(4000)，@start int,@end int
set @sql="select * from (select Row_NUMBER() OVER(order by "+@PkField+" desc) rowId,* from "+@TableName+" where "+@SqlWhere
set @start = (@PageIndex-1)*@PageSize+1
set @end = @start+@PageSize-1
set @sql = @sql + ") t where rowId between "+CAST(@start as varchar(20))+" and " +CAST(@end as varchar(20))
exec (@sql)
set @sql = "select @RowCount=count(1) from "+@TableName+" where "+@SqlWhere
exec sp_executesql @sql,N"@RowCount bigint OUTPUT",@RowCount OUTPUT
if(@RowCount%@PageSize=0)
begin
set @PageCount = @RowCount / @PageSize
end
else
begin
set @PageCount = @RowCount / @PageSize +1
end