SQL分页

 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE proc [dbo].[up_Page2005]
@TableName varchar(50),        --表名
@Fields varchar(5000) = '*',    --字段名(全部字段为*)
@OrderField varchar(5000),        --排序字段(必须!支持多字段)
@sqlWhere varchar(5000) = Null,--条件语句(不用加where)
@pageSize int,                    --每页多少条记录
@pageIndex int = 1 ,            --指定当前为第几页
@TotalPage int output            --返回总页数
as
begin

    Begin Tran --开始事务

    Declare @sql nvarchar(4000);
    Declare @totalRecord int;   

    --计算总记录数
        
    if (@SqlWhere='' or @sqlWhere=NULL)
        set @sql = 'select @totalRecord = count(*) from ' + @TableName
    else
        set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' with(nolock) where ' + @sqlWhere

    EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数       
   
    --计算总页数
    select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)

    if (@SqlWhere='' or @sqlWhere=NULL)
        set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName
    else
        set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' with(nolock) where ' + @SqlWhere   
       

    --处理页数超出范围情况
    if @PageIndex<=0
        Set @pageIndex = 1
   
    if @pageIndex>@TotalPage
        Set @pageIndex = @TotalPage

     --处理开始点和结束点
    Declare @StartRecord int
    Declare @EndRecord int
   
    set @StartRecord = (@pageIndex-1)*@PageSize + 1
    set @EndRecord = @StartRecord + @pageSize - 1

    --继续合成sql语句
    set @Sql = @Sql + ') as t where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' +  Convert(varchar(50),@EndRecord)
     print @sql  
    Exec(@Sql)
    ---------------------------------------------------
    If @@Error <> 0
      Begin
        RollBack Tran
        Return -1
      End
     Else
      Begin
        Commit Tran
        Return @totalRecord ---返回记录总数
      End   
end

 

 

 

 

 

 

 

应一个朋友的要求，贴上收藏的SQL常用分页的办法～～

表中主键必须为标识列，[ID] int IDENTITY (1,1)

1.分页方案一：(利用Not In和SELECT TOP分页)

语句形式： 
SELECT TOP 页记录数量 *
FROM 表名
WHERE (ID NOT IN
  (SELECT TOP (每页行数*(页数-1)) ID
  FROM 表名
  ORDER BY ID))
  ORDER BY ID
//自己还可以加上一些查询条件


例:
select top 2 *
from Sys_Material_Type
where (MT_ID not in
    (select top (2*(3-1)) MT_ID from Sys_Material_Type  order by MT_ID))
order by MT_ID



2.分页方案二：(利用ID大于多少和SELECT TOP分页）

语句形式：
SELECT TOP 每页记录数量 *
FROM 表名
WHERE (ID >
          (SELECT MAX(id)
    FROM (SELECT TOP 每页行数*页数 id  FROM 表
          ORDER BY id) AS T)
      )
ORDER BY ID

例:
SELECT TOP 2 *
FROM Sys_Material_Type
WHERE (MT_ID >
          (SELECT MAX(MT_ID)
          FROM (SELECT TOP (2*(3-1)) MT_ID
                FROM Sys_Material_Type
                ORDER BY MT_ID) AS T))
ORDER BY MT_ID



3.分页方案三：(利用SQL的游标存储过程分页)
create  procedure SqlPager
@sqlstr nvarchar(4000), --查询字符串
@currentpage int, --第N页
@pagesize int --每页行数
as
set nocount on
declare @P1 int, --P1是游标的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1, @rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off



4.总结:
其它的方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。
建议优化的时候，加上主键和索引，查询效率会提高。

通过SQL 查询分析器，显示比较：我的结论是:
分页方案二：(利用ID大于多少和SELECT TOP分页）效率最高，需要拼接SQL语句
分页方案一：(利用Not In和SELECT TOP分页)  效率次之，需要拼接SQL语句
分页方案三：(利用SQL的游标存储过程分页)    效率最差，但是最为通用