数据库分页处理--使用存储过程实现

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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值