实现千万级数据的分页显示

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

上一篇：死锁的问题

下一篇：本人用foxpro写的房屋产权产籍管理系统 (未完)