[SqlServer,分页存储过程] 分页存储过程

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