高效分页方法代码（sql百万级数据量分页代码)

高效分页方法代码（sql百万级数据量分页代码)

@querystr nvarchar(300),--表名、视图名、查询语句
@pagesize int=10,--每页的大小(行数)
@pagecurrent int=1,--要显示的页
@fdshow nvarchar (100)='',--要显示的字段列表,如果查询结果有标识字段,需要指

定此值,且不包含标识字段
@fdorder nvarchar (100)='',--排序字段列表
@wherestr nvarchar (200)='',     --内容是' id=3 and model_no like '%24%'

and '
@rscount int=0 output
as
set @fdshow=' '+@fdshow+' '
set @fdorder= ' '+@fdorder+' '
set @wherestr= ' '+@wherestr+' '

declare @fdname nvarchar(250)--表中的主键或表、临时表中的标识列名
,@id1 varchar(20),@id2 varchar(20)--开始和结束的记录号
,@obj_id int --对象id
,@temp nvarchar(300) --临时语句
,@strparam nvarchar(100) --临时参数

declare @strfd nvarchar(2000)--复合主键列表
,@strjoin nvarchar(4000)--连接字段
,@strwhere nvarchar(2000)--查询条件
--检查输入参数
set @querystr=ltrim(rtrim(@querystr))
select @obj_id=object_id(@querystr)
,@fdshow=case isnull(@fdshow,'') when '' then ' *' else ' '+@fdshow end
,@fdorder=case isnull(@fdorder,'') when '' then '' else ' order by

'+@fdorder end
,@querystr=case when @obj_id is not null then ' '+@querystr else '

('+@querystr+') a' end
--输出总记录数
set @temp= 'select @rscount=count(*)  from ' + @querystr+' '+@wherestr
set @strparam = n'@rscount int out'
execute sp_executesql  @temp,@strparam,@rscount out
--如果显示第一页，可以直接用top来完成
if @pagecurrent=1
begin
select @id1=cast(@pagesize as varchar(20))
exec('select top '+@id1+@fdshow+' from '+@querystr+@wherestr+@fdorder)
return
end
--如果是表,则检查表中是否有标识更或主键
if @obj_id is not null and objectproperty(@obj_id,'istable')=1
begin
select @id1=cast(@pagesize as varchar(20))
,@id2=cast((@pagecurrent-1)*@pagesize as varchar(20))
select @fdname=name from syscolumns where id=@obj_id and status=0x80
if @@rowcount=0--如果表中无标识列,则检查表中是否有主键
begin
if not exists(select 1 from sysobjects where parent_obj=@obj_id and

xtype='pk')
goto lbusetemp--如果表中无主键,则用临时表处理
select @fdname=name from syscolumns where id=@obj_id and colid in(
select colid from sysindexkeys where @obj_id=id and indid in(
select indid from sysindexes where @obj_id=id and name in(
select name from sysobjects where xtype='pk' and parent_obj=@obj_id
)))

if @@rowcount>1--检查表中的主键是否为复合主键
begin
select @strfd='',@strjoin='',@strwhere=''
select @strfd=@strfd+',['+name+']'
,@strjoin=@strjoin+' and a.['+name+']=b.['+name+']'
,@strwhere=@strwhere+' and b.['+name+'] is null'
from syscolumns where id=@obj_id and colid in(
select colid from sysindexkeys where @obj_id=id and indid in(
select indid from sysindexes where @obj_id=id and name in(
select name from sysobjects where xtype='pk' and parent_obj=@obj_id
)))
select @strfd=substring(@strfd,2,2000)
,@strjoin=substring(@strjoin,5,4000)
,@strwhere=substring(@strwhere,5,4000)
goto lbusepk
end
end
end
else
goto lbusetemp
/*--使用标识列或主键为单一字段的处理方法--*/
lbuseidentity:
        if len(@wherestr)>10
begin
exec('select top '+@id1+@fdshow+' from '+@querystr
+@wherestr+' and '+@fdname+' not in(select top '
+@id2+' '+@fdname+' from '+@querystr+@wherestr+@fdorder
+')'+@fdorder
)
return
end
else
begin
exec('select top '+@id1+@fdshow+' from '+@querystr
+' where '+@fdname+' not in(select top '
+@id2+' '+@fdname+' from '+@querystr+@fdorder
+')'+@fdorder
)
return
end
/*--表中有复合主键的处理方法--*/
lbusepk:
exec('select '+@fdshow+' from(select top '+@id1+' a.* from
(select top 100 percent * from '+@querystr+@fdorder+') a
left join (select top '+@id2+' '+@strfd+'
from '+@querystr+@fdorder+') b on '+@strjoin+'
where '+@strwhere+') a'
)
return
/*--用临时表处理的方法--*/
lbusetemp:
select @fdname='[id_'+cast(newid() as varchar(40))+']'
,@id1=cast(@pagesize*(@pagecurrent-1) as varchar(20))
,@id2=cast(@pagesize*@pagecurrent-1 as varchar(20))
exec('select '+@fdname+'=identity(int,0,1),'+@fdshow+'
into #tb from'+@querystr+@fdorder+'
select '+@fdshow+' from #tb where '+@fdname+' between '
+@id1+' and '+@id2
)