分页显示

来源：互联网发布：交换机端口亮黄灯编辑：程序博客网时间：2024/05/19 04:07

CREATE PROCEDURE Paging --创建分页数据集 Table[0]:记录总数和总页数, Table[1]:当前页
@pagesize int, -- 指定分页大小
@pagenum int, -- 指定当前页
@key varchar(50), -- 查询主键
@fields varchar(512), --指定除了主键和外其它需要输出的字段
@joins varchar(512), --联合数据源
@filter varchar(512), --查询条件
@order varchar(50) --排序规则
AS
declare @sql1 varchar(1024)
declare @sql2 varchar(1024)
declare @sql3 varchar(1024)
set @sql1 = 'select identity(int, 1, 1) as seqId, convert(varchar(50), ' + @key + ') as pkey into #tmp from ' + @joins + ' where ' + @filter + ' order by ' + case when charindex(@key, @order) > 0 then @order else @order + ', ' + @key end
set @sql2 = 'select @@rowcount as 记录总数, ceiling(@@rowcount*1.0 / ' + Convert(varchar(10), @pagesize) + ') as 总页数'
set @sql3 = 'select ' + @key + ' as 主键, #tmp.seqid as 序号, ' + @fields + ' from ' + @joins + ' inner join #tmp on ' + @key + ' = #tmp.pkey ' + ' where ' + @filter + ' and #tmp.seqid between ' + convert(varchar(10), (@pagenum-1)*@pagesize + 1) + ' and ' + convert(varchar(10), @pagenum*@pagesize) + ' order by #tmp.seqid'
exec ( @sql1 + ';' + @sql2 + ';' + @sql3 )
GO