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
【SqlServer 分页存储过程】相关文章：
★ sqlserver 通用存储过程分页代码(附使用ROW_NUMBER()和不使用ROW_NUMBER()两种情况性能分析)
★ sqlserver关于分页存储过程的优化【让数据库按我们的意思执行查询计划】
★ 如何在SQL SERVER 2005存储过程中,使用循环语句