SQL Server 分页查询存储过程代码

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,''