复制代码 代码如下:
create procedure [dbo].[up_pager]
@table varchar(2000), –表名
@col varchar(50), –按该列来进行分页
@orderby bit, –排序,0-顺序,1-倒序
@collist varchar(800),–要查询出的字段列表,*表示全部字段
@pagesize int, –每页记录数
@page int, –指定页
@condition varchar(800) –查询条件
as
declare @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800),
@total_item int,@total_page int
if @condition is null or rtrim(@condition)=”
begin–没有查询条件
set @where1=’ where ‘
set @where2=’ ‘
end
else
begin–有查询条件
set @where1=’ where (‘+@condition+’) and ‘–本来有条件再加上此条件
set @where2=’ where (‘+@condition+’) ‘–原本没有条件而加上此条件
end
set @sql=’select @total_item=ceiling((count(*)+0.0)’+’) from ‘+@table+ @where2
exec sp_executesql @sql,n’@total_item int output’,@total_item output –计算总条数
set @total_page = ceiling((@total_item+0.0)/@pagesize) –计算页总数
if @orderby=0
set @sql=’select top ‘+cast(@pagesize as varchar)+’ ‘+@collist+
‘ , ‘+ cast(@total_item as varchar) + ‘ as total_item’ +
‘ , ‘+cast(@total_page as varchar) + ‘ as total_page’ +
‘ from mailto:’+@table+@where1+@col+’%3e(select max(‘+@col+’) ‘+
‘ from (select top ‘+cast(@pagesize*(@page-1) as varchar)+’ ‘+
@col+’ from ‘+@table+@where2+’order by ‘+@col+’) t) order by ‘+@col
else
set @sql=’select top ‘+cast(@pagesize as varchar)+’ ‘+@collist+
‘ , ‘+ cast(@total_item as varchar) + ‘ as total_item’ +
‘ , ‘+cast(@total_page as varchar) + ‘ as total_page’ +
‘ from mailto:’+@table+@where1+@col+’%3c(select min(‘+@col+’) ‘+
‘ from (select top ‘+cast(@pagesize*(@page-1) as varchar)+’ ‘+
@col+’ from ‘+@table+@where2+’order by ‘+@col+’ desc) t) order by ‘+
@col+’ desc’
if @page=1–第一页
set @sql=’select top ‘+cast(@pagesize as varchar)+’ ‘+@collist+
‘ , ‘+ cast(@total_item as varchar) + ‘ as total_item’ +
‘ , ‘+cast(@total_page as varchar) + ‘ as total_page’ +
‘ from ‘+@table+
@where2+’order by ‘+@col+case @orderby when 0 then ” else ‘ desc’ end
–print @sql
exec(@sql)
在sql中测试(教你如何使用)
复制代码 代码如下:
exec up_pager ‘(select * from 表名)aa’,’要排序的列名’,0-顺序或1-倒序,’显示列’,每页记录数,指定页,’条件’
exec up_pager ‘(select * from t_gather_page)aa’,’savetime’,1,’*’,40,3,”