因为 EntityFramework 等 ORM 框架的出现,分页存储过程的使用越来越少,本篇记录的存储过程使用 ROW_NUMBER 方式分页,在查询效率上面还是非常不错的,因为这个分页存储过程采用的是字符串拼接,所以要保证传入参数的安全性,存储过程代码如下:
create proc [dbo].[P_Pagination]
(
@FieldSql varchar(4000), --查询列列表
@Field varchar(1000), --查询字段名
@TableName varchar(50), --表名
@PrimaryKey varchar(50), --主键名
@PageIndex int, --页的索引
@PageSize int, --页的大小
@WhereSql varchar(1000), --条件语句,
@OrderSql varchar(1000), --排序语句
@JoinSql varchar(1000), -- 连接语句
@PageCount int output, --页总数
@TotalCount int output --记录总数
)
as
declare @Sql varchar(max)
declare @Sql1 nvarchar(max)
if @Field = ''
begin
set @Field = @FieldSql
end
if @OrderSql = ''
begin
set @OrderSql = @PrimaryKey + ' desc '
end
if @WhereSql<>''
begin
set @Sql = 'select ' + @FieldSql + ' from (select ROW_NUMBER() over(order by ' + @OrderSql + ') AS RowNums,' + @Field + ' from ' + @TableName + ' {0} ' + @JoinSql + ' where ' + @WhereSql + ') AS ' + @TableName + ' where RowNums between ' + Str((@PageIndex-1) * @PageSize + 1) + ' and ' + Str(@PageIndex * @PageSize)
set @Sql1 = N'Select @Count=Count(0) from ['+@TableName+'] {0} ' + @JoinSql + ' where '+@WhereSql
end
else
begin
set @Sql = 'select ' + @FieldSql + ' from (select ROW_NUMBER() over(order by ' + @OrderSql + ') AS RowNums,' + @Field + ' from ' + @TableName + ' {0} ' + @JoinSql + ' ) AS ' + @TableName + ' where RowNums between ' + Str((@PageIndex-1) * @PageSize + 1) + ' and ' + Str(@PageIndex * @PageSize)
set @Sql1 = N'Select @Count=Count(0) from ['+@TableName+'] {0} ' + @JoinSql + ''
end
execute sp_executesql @Sql1, N'@Count int output',@Count=@TotalCount output
set @PageCount = ceiling(convert(float,@TotalCount)/@PageSize)
exec (@Sql)