分页存储过程（转）

CREATE PROCEDURE [dbo].[UP_CutPage]
    @tbName      nvarchar(255), -- 表名(允许多张表的join)
    @keyorder      nvarchar(255), -- 用于排序的字段名 后面可以追加' asc' 或' desc', 不支持对多个字段排序
    @keymain      nvarchar(255)='', -- 主键字段名(如果用于排序的字段的值是唯一的，应将此参数设为''或null(不这样将会降低效率); 如果用于排序的字段的值不是唯一的， 则需要写真实的主键) 后面可以追加' asc' 或' desc', 不支持多个主键
    @columns nvarchar(1000)='*', -- 要选择的列 (注意: 本存储过程不会自动给列名或表名加方括号)
    @strWhere     nvarchar(3000) = '',  -- 查询条件 (注意: 不要加 where)
    @PageSize     int = 10, -- 页尺寸
    @PageIndex    int = 1 output, -- 页码
    @RowsCount    int=0 output, -- 记录总数,
    @PageCount int=0 output --页面总数
AS
begin

declare @s1 nvarchar(100) -- 临时变量
declare @s2 nvarchar(100) -- 临时变量

declare @strSQL   nvarchar(4000) -- 主语句
declare @strTmp   nvarchar(3000) -- 临时变量
declare @strOrder nvarchar(2000) -- 排序类型

--取得总行数
if @strWhere != ''
 set @strSQL='SELECT @RowsCount=count(*) FROM '+@tbName+' where ' + @strWhere
else
 set @strSQL='SELECT @RowsCount=count(*) FROM '+@tbName
exec sp_executesql @strSQL, N'@RowsCount int out', @RowsCount out
set @strSQL=''

--计算总页数  
set @PageCount=@RowsCount/@PageSize
if (@RowsCount % @PageSize<>0)
 set @PageCount=@PageCount+1
if (@PageCount<1)
 set @PageCount=1

--错误处理
if (@PageSize<1)
 set @PageSize=1
if (@PageIndex<1)
 set @PageIndex=1
if (@PageIndex>@PageCount)
 set @PageIndex=@PageCount

--拼接Order By子句
declare @strOrderType1 nvarchar(50)
declare @strOrderType2 nvarchar(50)
if(CHARINDEX(' desc', @keyorder)>0)
begin
    set @strOrderType1='desc'
    set @keyorder=REPLACE(@keyorder, ' desc', '')
end
else
begin
    set @strOrderType1='asc'
    set @keyorder=REPLACE(@keyorder, ' asc', '')
end
----求字段类型start
    declare @OrderTable nvarchar(255)
    declare @OrderName nvarchar(255)
    declare @OrderType varchar(255)
    declare @OrderPrec varchar(50)
    declare @OrderDot int
    set @OrderDot=CHARINDEX('.', @keyorder)
    IF @OrderDot > 0
        BEGIN
            SET @OrderTable = SUBSTRING(@keyorder, 0, @OrderDot)
            SET @OrderName = SUBSTRING(@keyorder, @OrderDot + 1, LEN(@keyorder))
        END
    ELSE
        BEGIN
            SET @OrderTable = @tbName
            SET @OrderName = @keyorder
        END
    set @s1=REPLACE(REPLACE (@OrderTable,'[',''),']','')
    set @s2=REPLACE(REPLACE (@OrderName,'[',''),']','')
    SELECT @OrderType=t.[name], @OrderPrec=c.prec
    FROM sysobjects o
    JOIN syscolumns c on o.id=c.id
    JOIN systypes t on c.xusertype=t.xusertype
    WHERE o.name = @s1 AND c.[name] = @s2
    IF CHARINDEX('char', @OrderType) > 0
       SET @OrderType = @OrderType + '(' + CAST(@OrderPrec AS varchar) + ')'
----求字段类型end
if @strOrderType1='asc'
 begin
  set @strOrderType1 = '>'
  set @strOrder = ' order by ' + @keyorder +' asc'
 end
else
 begin
  set @strOrderType1 = '<'
  set @strOrder = ' order by ' + @keyorder +' desc'
 end
if @keymain is null
 set @keymain=''
if @keymain<>''
begin
    if(CHARINDEX(' desc', @keymain)>0)
    begin
        set @strOrderType2='desc'
        set @keymain=REPLACE(@keymain, ' desc', '')
    end
    else
    begin
        set @strOrderType2='asc'
        set @keymain=REPLACE(@keymain, ' asc', '')
    end
----求字段类型start
    declare @MainTable nvarchar(255)
    declare @MainName nvarchar(255)
    declare @MainType varchar(255)
    declare @MainPrec varchar(50)
    declare @MainDot int
    set @MainDot=CHARINDEX('.', @keymain)
    IF @MainDot > 0
        BEGIN
            SET @MainTable = SUBSTRING(@keymain, 0, @MainDot)
            SET @MainName = SUBSTRING(@keymain, @MainDot + 1, LEN(@keymain))
        END
    ELSE
        BEGIN
            SET @MainTable = @tbName
            SET @MainName = @keymain
        END
    set @s1=REPLACE(REPLACE (@MainTable,'[',''),']','')
    set @s2=REPLACE(REPLACE (@MainName,'[',''),']','')
    SELECT @MainType=t.name, @MainPrec=c.prec
    FROM sysobjects o
    JOIN syscolumns c on o.id=c.id
    JOIN systypes t on c.xusertype=t.xusertype
    WHERE o.name = @s1 AND c.name = @s2
    IF CHARINDEX('char', @MainType) > 0
       SET @MainType = @MainType + '(' + CAST(@MainPrec AS varchar) + ')'
----求字段类型end
 if @strOrderType2='asc'
  begin
   set @strOrderType2='>'
   set @strOrder =@strOrder + ', ' + @keymain +' asc'
  end
 else
  begin
   set @strOrderType2='<'
   set @strOrder =@strOrder + ', ' + @keymain +' desc'
  end
end

--拼接查询语句
if @PageIndex = 1
 begin
  set @strTmp =''
  if @strWhere != ''
   set @strTmp = ' where ' + @strWhere
  set @strSQL = 'select top ' + convert(nvarchar(100),@PageSize) + ' '+@columns+' from '
   + @tbName + ' ' + @strTmp + ' ' + @strOrder
 end
else
 begin
  --求临界值
  set @strSQL='declare @midData '+@OrderType+';'
  if @MainType is not null
     set @strSQL=@strSQL+'declare @midID '+@MainType+';'
  set @strSQL=@strSQL+'SET ROWCOUNT '+ convert(nvarchar(100),(@PageIndex-1)*@PageSize)+';'        
  if  @keymain<>''
   set @strSQL =@strSQL+ 'select @midData= '
    + @keyorder + ', @midID=' + @keymain + ' from ' + @tbName
  else
   set @strSQL =@strSQL+ 'select @midData= '
    + @keyorder + ' from ' + @tbName
  if @strWhere != ''
   set @strSQL=@strSQL+ ' where ' + @strWhere
  set @strSQL=@strSQL+' '+@strOrder+';'
  --print (@strSQL); return
  --最终查询语句
  set @strSQL=@strSQL+'SET ROWCOUNT '+ convert(nvarchar(100),@PageSize) +';'        
  if  @keymain<>''
   set @strSQL =@strSQL+ 'select '+@columns+' from '
    + @tbName + ' where (' + @keyorder + @strOrderType1 + '@midData or ('
    + @keyorder + '=@midData and ' + @keymain + @strOrderType2 +'@midID))'
  else
   set @strSQL =@strSQL+ 'select '+@columns+' from '
    + @tbName + ' where ' + @keyorder + @strOrderType1 + '@midData'
  if @strWhere <> ''
   set @strSQL=@strSQL+' and '+ @strWhere
  set @strSQL=@strSQL+' ' + @strOrder
 end

--执行查询语句
--print (@strSQL); return
exec (@strSQL)

/*
------------调用示例-------------------
DECLARE @tbName nvarchar(255) -- 表名(允许多张表的join)
DECLARE @keyorder nvarchar(255) -- 用于排序的字段名 后面可以追加' asc' 或' desc', 不支持对多个字段排序
DECLARE @keymain nvarchar(255) -- 主键字段名(如果用于排序的字段的值是唯一的，应将此参数设为''或null(不这样将会降低效率); 如果用于排序的字段的值不是唯一的， 则需要写真实的主键) 后面可以追加' asc' 或' desc', 不支持多个主键
DECLARE @columns nvarchar(1000) -- 要选择的列 (注意: 本存储过程不会自动给列名或表名加方括号)
DECLARE @strWhere nvarchar(3000) -- 查询条件 (注意: 不要加 where)
DECLARE @PageSize int -- 页尺寸
DECLARE @PageIndex int -- 页码
DECLARE @RowsCount int -- 记录总数,
DECLARE @PageCount int -- 页面总数

select
@tbName='tab1 inner join tab2 on tab1.id=tab2.id',
@keyorder='tab1.id',
@keymain ='tab2.id asc',
@columns ='tab1.*,tab2.note',
@strWhere= 'tab2.id % 2 = 1 and tab1.id>6', 
@PageSize = 5,
@PageIndex = 2,
@RowsCount =1 ,
@PageCount =1

EXECUTE [dbo].[UP_CutPage]
   @tbName
  ,@keyorder
  ,@keymain
  ,@columns
  ,@strWhere
  ,@PageSize
  ,@PageIndex OUTPUT
  ,@RowsCount OUTPUT
  ,@PageCount OUTPUT
*/

end

/*
在所有分页存储过程的代码里，我的这份代码估计是最长的，我为何执意要写这样的代码呢？理由其实只有两个
通常分页存储过程只能按主键排序，而我这个允许按非主键排序
通常分页存储过程不会OUTPUT参数@PageIndex和@PageCount，而我这个则是例外
这个分页存储过程的代码虽然很长，但在百万级记录数的测试中， 其执行速度微微快于一般的分页存储过程

这个分页存储过程并非完全由我所写，是参考了几个比较好的分页存储过程而写的，当然, 所有分页存储过程的基本思想都是差不多的...

*/