数据库端分页存储过程

       近日项目中要用到大批量的数据分页显示，而VS2003中的DATAGRID中的分页机制是整个的读出数据到内存，再将内存中符合分页条件的数据显示到页面，如果数据量达到一定程序会占用大量系统资源，导致整个程序反应迟钝。 因而只好采用在数据库端分页的方法，每次只读出指定的记录数。

CREATE  PROCEDURE dbo.proc_Pagination  
(  
      @tblName varchar(50),--查询的表名或视图片
      @pageIndex int output ,    --查询记录页码数
      @pageSize int ,     -- 每页记录数
      @keyID  varchar(10),  --表或视图的主键
      @where varchar(200) = null ,   --条件表达
      @order varchar(200)= null,   --排序表达式
      @pageCount int output,   --总的页数
      @rowsCount int output --总的记录数
)
AS
BEGIN

    if (@order is not null and  @order <> '')
    begin
        set @order = ' order by ' + @order
    end

   if ( @pageIndex < 1)
   begin
       set @pageIndex = 1
   end

    --计算总的记录数
    declare @strByRows nvarchar(1000)
    SET @strByRows = 'select @pages = count(*) from ' + @tblName
    if ( @where is not null and @where <> '')
    begin
        SET @strByRows  =  @strByRows +  ' where ' + @where
    end

    EXEC sp_executesql @strByRows,N'@pages int OUTPUT',@rowsCount OUTPUT--计算总页数

 if ( @rowsCount  > 0)
  begin  

  --计算总的页数
    if ( @rowsCount % @pageSize = 0)
    begin 
        set @pageCount  = @rowsCount/@pageSize
    end
    else
    begin
          set @pageCount  = @rowsCount/@pageSize + 1
    end
  
    --如果当前页大于总的页数，则设置当前页码为总的页数
    if ( @pageIndex > @pageCount)
    begin
       set  @pageIndex = @pageCount
    end

    --查询第N页数据
    declare @str varchar(8000)

    declare @strByIn varchar(5000)
    declare @strByNotIn varchar(5000)

    set  @strByIn = 'select top ' + Convert(varchar(10),@pageIndex * @pageSize) + ' '+ @keyID + ' from '+ @tblName
    if ( @where is not null and @where <> '')
    begin
       set @strByIn = @strByIn + ' where ' + @where
    end
    if ( @order is not null and @order <> '')
    begin
       set @strByIn = @strByIn + @order
    end

    set @strByNotIn = 'select top '  + Convert(varchar(10), (@pageIndex-1) * @pageSize) + ' '+ @keyID + ' from ' + @tblName
    if ( @where is not null and @where <> '')
    begin
        set @strByNotIn = @strByNotIn + ' where ' + @where
    end
    if ( @order is not null and @order <> '')
    begin
       set @strByNotIn = @strByNotIn  + @order
    end

    set @str = 'select * from ' + @tblName
    set @str = @str + ' where ' + @keyID + ' in(' + @strByIn + ') '
    set @str = @str + ' and ' + @keyID + ' not in(' + @strByNotIn + ') '
    if ( @where is not null and @where <> '')
    begin
         set @str = @str + ' and ' + @where
    end
    if ( @order is not null and @order <> '')
    begin
       set @str = @str + @order
    end

end
else
begin 
     set @str = 'select  top 0 * from ' + @tblName
end
     exec(@str)

END

存储过程调用代码
declare @pageCount int
declare  @rowsCount int
declare @pageIndex int
set @pageIndex = 2
exec proc_Pagination @tblName = 'tblName',@where = '',@order = 'id',@pageIndex=@pageIndex output ,@pageSize=100,@keyID='id',@pageCount = @pageCount output,@rowsCount = @rowsCount output

select @pageCount,@rowsCount,@pageIndex 

 

此存储过程为一通用存储过程，只要提供要查询的表名事视图名与查询页码数、每页记录数等信息就可以。

存储过程还存在一个小问题，如果读取的数据的页码数字越大其性能越低速度越慢，暂未找到好的解决之法。