最近在做一个几百万条数据的分页查询，研究了各种方案，在本机上用项目的实际数据库做测试，测试过程 is very 痛苦，不堪回首ing。现在废话不多说，直接上结果，相信这也是大多数搜索答案的人最愿意看的方式。
以下是存储过程的代码：
复制代码 代码如下:
create procedure [dbo].[p_gridviewpager] (
@recordtotal int output, –输出记录总数
@viewname varchar(800), –表名
@fieldname varchar(800) = ‘*’, –查询字段
@keyname varchar(200) = ‘id’, –索引字段
@pagesize int = 20, –每页记录数
@pageno int =1, –当前页
@orderstring varchar(200), –排序条件
@wherestring varchar(800) = ‘1=1’ –where条件
)
as
begin
declare @beginrow int
declare @endrow int
declare @templimit varchar(200)
declare @tempcount nvarchar(1000)
declare @tempmain varchar(1000)
–declare @timediff datetime
set nocount on
–select @timediff=getdate() –记录时间
set @beginrow = (@pageno – 1) * @pagesize + 1
set @endrow = @pageno * @pagesize
set @templimit = ‘rows between ‘ + cast(@beginrow as varchar) +’ and ‘+cast(@endrow as varchar)
–输出参数为总记录数
set @tempcount = ‘select @recordtotal = count(*) from (select ‘+@keyname+’ from ‘+@viewname+’ where ‘+@wherestring+’) as my_temp’
execute sp_executesql @tempcount,n’@recordtotal int output’,@recordtotal output
–主查询返回结果集
set @tempmain = ‘select * from (select row_number() over (order by ‘+@orderstring+’) as rows ,’+@fieldname+’ from ‘+@viewname+’ where ‘+@wherestring+’) as main_temp where ‘+@templimit
–print @tempmain
execute (@tempmain)
–select datediff(ms,@timediff,getdate()) as 耗时
set nocount off
end
go