實現千萬級數據的分頁顯示
2024-07-21 02:11:25
供稿：網友
這是經我該寫后的存儲過程
---------------------------------------------------------
alter procedure usp_getrecordfrompage
@tblname varchar(1000), -- 表名
@selectfieldname varchar(4000), -- 要顯示的字段名(不要加select)
@strwhere varchar(4000), -- 查詢條件(注意: 不要加 where)
@orderfieldname varchar(255), -- 排序索引字段名
@pagesize int , -- 頁大小
@pageindex int = 1, -- 頁碼
@irowcount int output, -- 返回記錄總數
@ordertype bit = 0 -- 設置排序類型, 非 0 值則降序
as
declare @strsql varchar(4000) -- 主語句
declare @strtmp varchar(4000) -- 臨時變量
declare @strorder varchar(400) -- 排序類型
declare @strrowcount nvarchar(4000) -- 用于查詢記錄總數的語句
set @orderfieldname=ltrim(rtrim(@orderfieldname))
if @ordertype != 0
begin
set @strtmp = '<(select min'
set @strorder = ' order by ' + @orderfieldname +' desc'
end
else
begin
set @strtmp = '>(select max'
set @strorder = ' order by ' + @orderfieldname +' asc'
end
set @strsql = 'select top ' + str(@pagesize) + @selectfieldname+' from '
+ @tblname + ' where ' + @orderfieldname + @strtmp + '('
+ right(@orderfieldname,len(@orderfieldname)-charindex('.',@orderfieldname)) + ') from (select top ' + str((@pageindex-1)*@pagesize)
+ @orderfieldname + ' from ' + @tblname + @strorder + ') as tbltmp)'
+ @strorder
if @strwhere != ''
set @strsql = 'select top ' + str(@pagesize) + @selectfieldname+' from '
+ @tblname + ' where ' + @orderfieldname + @strtmp + '('
+ right(@orderfieldname,len(@orderfieldname)-charindex('.',@orderfieldname)) + ') from (select top ' + str((@pageindex-1)*@pagesize)
+ @orderfieldname + ' from ' + @tblname + ' where ' + @strwhere + ' '
+ @strorder + ') as tbltmp) and ' + @strwhere + ' ' + @strorder
if @pageindex = 1
begin
set @strtmp = ''
if @strwhere != ''
set @strtmp = ' where ' + @strwhere
set @strsql = 'select top ' + str(@pagesize) + @selectfieldname+' from '
+ @tblname + @strtmp + ' ' + @strorder
end
exec(@strsql)
if @strwhere!=''
begin
set @strrowcount = 'select @irowcount=count(*) from ' + @tblname+' where '[email protected]
end
else
begin
set @strrowcount = 'select @irowcount=count(*) from ' + @tblname
end
exec sp_executesql @strrowcount,n'@irowcount int out',@irowcount out
