GO

if @strWhere !=”
begin
    set @strWhere=’ where ‘+@strWhere
end
set @strSQL=’SELECT * FROM (SELECT ROW_NUMBER() OVER (‘+@strOrder+’) AS ROWID,’
set @strSQL=@strSQL+@strGetFields+’ FROM [‘+@tblName+’] ‘+@strWhere
set @strSQL=@strSQL+’) AS sp WHERE ROWID BETWEEN ‘+str((@PageIndex-1)*@PageSize+1)
set @strSQL=@strSQL+’ AND ‘+str(@PageIndex*@PageSize)
exec (@strSQL)

 

   
随着数据库中存储的数据的增多,满足用户查询条件的数据也随之增加。而用户一般不可能一次性看完所有的数据,
很多时候也不需要看完所有数据。在这种情况下,分页返回用户查询的数据就显得相当的重要。分页返回用户数据有如下好处:
1、减少服务器磁盘系统地读取压力
2、减少网络流量,减轻网络压力
3、减轻客户端显示数据的压力
4、提高处理效率。
     
一般而言,分页处理分为两种:应用程序中的分页处理和数据库中的分页处理。目前大多数的应用都是在应用程序中借助支持数据分页处理的数据库访问组件(如DataGrid控件)实现分页处理。实际上,在数据库中实现分页处理,可以从源头减少数据处理量,效果往往可能跟明显。本文主要讨论数据库的分页问题。
      常规的取第n页数据方法为: Select top PageSize * from TableA where
Primary_Key not in (select top (n-1)*PageSize Primary_Key from TableA
)。
     
对于应用程序而言,所做的就是在生成分页处理的T-SQL语句前先计算好各数字,
对于数据库而言,应该采用动态的T-SQL语句。
 以下是使用上述原理实现的通用分页处理存储过程:

  • ‘ From ‘ + @tableName + @where +  ‘ Order by ‘+ @orderCol +’))’
    select @sql = @sql + @where2
    select @sql = @sql + ‘ Order by ‘ + @orderCol
    –获取数据集
    exec (@sql)
    PRINT @sql
    select @sql2 = Replace(@sql2,’Top ‘ + Convert(varchar(10), @pageSize) +
    ‘ ‘ + @columns, ‘count(1)’)
    –获取总数据条数
    exec(@sql2)

declare @strCount nvarchar(1000)
–总记录条数
if(@doCount!=0)
begin
    if(@strWhere !=”)
    begin
        set @strCount=’set @num=(select count(1) from ‘+ @tblName + ‘ where ‘+@strWhere+’ )’
    end
    else
    begin
        set @strCount=’set @num=(select count(1) from ‘+ @tblName + ‘ )’
    end
    EXECUTE sp_executesql @strCount ,N’@num INT output’,@RecordCount output
end

技术交流群:171880541

本人声明,以上仅代表个人理解,且于个人水平有限, 如有错误,不妥的地方,希望多多包涵,
更希望路过高手能指正,以免误导读者。

         另外, sql server 2005
增加了一些新的功能如取得排名或顺序的函数(Rank(), Dense_Rank(),
Row_Number()), 利用这些新的功能也能进行分页处理,下面以sql server 2005
自带的数据库AdventureWorks为例结合Row_Number() 实现分页处理:

if @where <> ”
begin
    select @where2 = ‘ And ‘ + @where
    select @where = ‘ Where ‘ + @where
end
else
    select @where2 = ”

— =============================================
— Author:        <jiangrod>
— Create date: <2010-03-03>
— Description:    <SQL2000通用分页存储过程>调用方法: sp_Pager2000 ‘xtest’,’*’,’ID’,’ORDER BY ID ASC’,’xname like ”%222name%”’,3,20,0,0
— =============================================
ALTER PROCEDURE [dbo].[sp_Pager2000]
@tblName   varchar(255),            — 表名如:’xtest’
@strGetFields varchar(1000) = ‘*’,  — 需要返回的列如:’xname,xdemo’
@pkName        nvarchar(50)=’ID’,        — 主键名
@strOrder varchar(255)=”,          — 排序的字段名如:’order by id desc’
@strWhere  varchar(1500) = ”,      — 查询条件(注意:不要加where)如:’xname like ”%222name%”’ 
@pageIndex  int = 1,                — 页码如:2
@pageSize   int = 20,               — 每页记录数如:20
@recordCount int=0 out,             — 记录总数
@doCount bit=0                        — 非0则统计,为0则不统计(统计会影响效率)
AS

   set @sqlPaging=@sqlPaging+N’where tmp.RowIndex between
‘+Convert(varchar(50),((@pageIndex-1)*@pageSize+1))+’ and
‘+cast((@pageIndex*@pageSize) as varchar(50))
   if(@orderCondition is not null and
len(RTRIM(LTRIM(@orderCondition)))>0)
   begin
      set @sqlPaging=@sqlPaging+’ order by ‘+ @orderCondition
   end
   exec sp_executesql @stmt=@sqlPaging
end
go
declare @sum int,@rowCount int
exec usp_Paging1
@tableName=’Student’,@primaryKey=’id’,@orderCondition=’id
desc’,@pageIndex=1,@pageSize=20,@pageCondition=’id>10′,
     @pageCount=@sum output,@rowCount=@rowCount output
select @sum as 总页数,@rowCount 总记录条数

  • ‘ ‘
                    + @colshow + ‘ ‘ + ‘from ‘ + @tableName + ‘ ‘ + @where +
    ‘ ‘ + @colOrder
        else
            begin
            set @sql = ‘select top’ +’ ‘ + convert(nvarchar(10), @pageSize)
  • ‘ ‘
                    + @colshow + ‘ ‘ + ‘from ‘ + @tableName + ‘ ‘ + @where
            set @sql = @sql + ‘ ‘ + ‘and ‘+ @colKey +’ not in ( ‘
                    + ‘select top’ +’ ‘ + convert(nvarchar(10),
    (@pageCurrent – 1) * @pageSize) + ‘ ‘
                    + @colKey + ‘ ‘ + ‘from ‘ + @tableName + ‘ ‘ + @where  +
    ‘ )’
            set @sql = @sql + ‘ ‘ + @colOrder
            end
        –execute the dynamic query
        exec (@sql)
    end

相关文章