sql2000及以上通用分页存储过程

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO










ALTER  PROCEDURE  [dbo].[Paging](
@Tables varchar(1000), --表名,多红表是请使用 tA a inner join tB b On a.AID = b.AID
@Fields varchar(2000) = '*',--读取字段
@PK varchar(100),    --主键，可以带表头 a.AID
@Filter varchar(2000) = '',--Where条件
@SortField varchar(200) = '', --排序字段 id
@SortType varchar(100)='asc',--排序类型 asc
@PageIndex int = 1,    --开始页码
@PageSize int = 10,        --页大小
@GroupField varchar(1000) = '',  --分组(分组字段)
@isCount bit = 1 ,    --1    --是否获得总记录数
@recordCount int=0 output
)
AS 


DECLARE @strFilter varchar(2000)
declare @sql varchar(8000)
declare @recordSql nvarchar(2000)
declare @pageCount int
declare @newSortType varchar(100)
declare @newSortField varchar(100)
DECLARE @strGroupByFilter VARCHAR(2000)


IF @Filter IS NOT NULL AND @Filter != ''
  BEGIN
   SET @strFilter = ' WHERE ' + @Filter + ' '
  END
ELSE
  BEGIN
   SET @strFilter = ''
  END


IF @GroupField IS NOT NULL AND @GroupField != ''
  BEGIN
   SET @strGroupByFilter = ' Group by ' + @GroupField + ' '
  END
ELSE
  BEGIN
   SET @strGroupByFilter = ''
  END


if @isCount = 1 --获得记录条数
    BEGIN
       set @recordSql = N'DECLARE @RCount INT;
Select @recordCount=Count(0) FROM ' + @Tables + @strFilter+@strGroupByFilter+';SELECT @RCount=@@RowCount;if(@RCount>1)Set @recordCount=@RCount;'
EXEC sp_executesql @recordSql, N'@RecordCount INT OUT', @recordCount OUT
    END
    
if @SortField = ''
  set @SortField = @PK 


IF @PageIndex < 1
  SET @PageIndex = 1




if Upper(@SortType)='ASC'  --获得一个新的排序类型用于取最后一页的数据
begin
Set @newSortType='desc'
    end
else
begin
set @newSortType='asc'
end




if CharIndex('.',@SortField,0)>0 --获得排序字段(a.sbid) 得到 sbid
begin
Set @newSortField=right(@SortField,(len(@SortField)-CharIndex('.',@SortField,0)))
end
else
begin
Set @newSortField=@SortField
end


IF @PageIndex = 1 
begin
IF @PageIndex = 1 --返回第一页数据
begin
 set @sql = 'select top ' + str(@PageSize) +' '+@Fields+ '  from ' + @Tables + ' ' + @strFilter + @strGroupByFilter+ ' ORDER BY  '+ @SortField +' ' + @SortType  
END
ELSE IF @PageIndex >= CEILING((@recordCount+0.0)/@PageSize)  --返回最后一页数据
BEGIN
SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' * FROM (' 
  + 'SELECT TOP ' + STR(ABS(@PageSize*@PageIndex-@recordCount-@PageSize)) 
  + ' ' + @Fields + ' FROM '
  + @Tables + @strFilter +@strGroupByFilter + ' ORDER BY '+@SortField +' '+ @newSortType+' ) AS TMP order by TMP.'+@newSortField+ ' '+ @SortType
END
    END
else
  begin
   DECLARE @START_ID varchar(50)
DECLARE @END_ID varchar(50)
SET @START_ID = convert(varchar(50),(@PageIndex - 1) * @PageSize + 1)
SET @END_ID = convert(varchar(50),@PageIndex * @PageSize)
    set @sql =  ' SELECT *
   FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+ @SortField +' '+ @SortType+') AS rownum, 
     '+@Fields+ '
      FROM '+@Tables+' '+@strFilter+''+@strGroupByFilter+') AS D
   WHERE rownum BETWEEN '+@START_ID+' AND ' +@END_ID 
  END
EXEC(@sql)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO