CREATE PROCEDURE sp_pagination --分页存储过程(千万级分页有主键时适用)
(
@fieldlist varchar(500),--显示字段列表
@tablename varchar(50), --查询表的名称
@id varchar(50), --标识字段
@currentpage int, --当前第几页
@pagesize int, --每页显示多少条记录
@likelist varchar(8000), --判断条件列表
@orderlist varchar(1000), --排序语句列表
@recordcount int output --返回记录总数
)
as
declare @sql nvarchar(4000) -- 主语句
declare @recordtotal int --返回当前查询结果记录总数
declare @strtmp varchar(1000) -- 临时变量
declare @strorder varchar(1000) -- 排序类型
declare @order int --0降序,1是升序,依靠@orderlist决定
set @sql="select @reccount=count(["+@id+"]) from "+@tablename + " "+@likelist+"" --sql查询语句
execute sp_executesql @sql,N'@reccount int output',@reccount=@recordcount output --获取记录总数
if substring(ltrim(replace(replace(@orderlist,"order by",""),@id,"")),1,4)="desc"
begin
set @order =0 --降序
end
else
begin
set @order =1 --升序
end
if @order= 0
begin
set @strtmp = "<(select min"
set @strorder = " order by [" + @id +"] desc"
end
else
begin
set @strtmp = ">(select max"
set @strorder = " order by [" + @id +"] asc"
end
set @sql = "select top " + str(@pagesize) + " "+@fieldlist+" from ["+ @tablename + "] where [" + @id + "]" + @strtmp + "(["+ @id + "]) from (select top " + str((@currentpage-1)*@pagesize) + " ["+ @id + "] from [" + @tablename + "]" + @strorder + ") as tblTmp)"+ @strorder
set @likelist=replace(@likelist,"where","")
if @likelist != ''
begin
set @sql = "select top " + str(@pagesize) + " "+@fieldlist+" from ["+ @tablename + "] where [" + @id + "]" + @strtmp + "(["+ @id + "]) from (select top " + str((@currentpage-1)*@pagesize) + " ["+ @id + "] from [" + @tablename + "] where " + @likelist + " "+ @strorder + ") as tblTmp) and " + @likelist + " " + @strorder
end
if @currentpage = 1
begin
set @strtmp = ""
if @likelist != ''
set @strtmp = " where " + @likelist
set @sql = "select top " + str(@pagesize) + " "+@fieldlist+" from ["+ @tablename + "]" + @strtmp + " " + @strorder
end
exec(@sql)
GO
数据库分页处理--使用存储过程实现
最新推荐文章于 2024-04-09 16:51:57 发布