Sql2005数据库分页存储过程

-- =============================================
-- 作用:  数据库分页
-- =============================================
CREATE  PROCEDURE [dbo].[PROC_Pagination]
@TableName varchar(100),        --表名
@Select_List varchar(2000),     --查询列表(为*表示所有)
@KeyFieldsName varchar(100),    --主键(必须有主键或聚集索引列,最好是int或bigint,为提高查询速度)
@TaxisFielesName varchar(100),  --结果页排序字段名
@TaxisFashion int,              --结果页的排序方式0是升序,其他是降序
@PageSize int,                  --页大小
@CurrentPage int,               --当前页
@SqlWhere varchar(3500),        --Sql条件部份(不要加Where)
@RowCount int output            --返回行总数
AS
BEGIN 
declare @strSQL  varchar(8000)       -- 主语句
declare @strTaxis varchar(10)        --升降序
declare @strRunRowSQL nvarchar(4000) --行数语句
declare @iCount int                  --临时统计行
declare @strTmp   varchar(200)       -- 临时变量
declare @strOrder varchar(500)       -- 排序类型

   if(@SqlWhere is null)
     set @SqlWhere=''  

   if @CurrentPage=0
     set @CurrentPage=1  --如果当前页是0，则为1
   if @TaxisFashion !=0
     BEGIN
       set @strTaxis='DESC'
       set @strTmp = '<(select min'
       set @strOrder = ' order by [' + @KeyFieldsName +'] desc'
     END
      else
     BEGIN
       set @strTaxis='ASC'
       set @strTmp = '>(select max'
       set @strOrder = ' order by [' + @KeyFieldsName +'] asc'
     END

    if @SqlWhere=''
       set @strRunRowSQL=N'SELECT @iCount=COUNT(*) FROM ' + @TableName
     else
       set @strRunRowSQL=N'SELECT @iCount=COUNT(*) FROM ' + @TableName + ' WHERE ' + @SqlWhere

  if(@CurrentPage=1)  --如果是第一页
    BEGIN
      if @SqlWhere != ''  
       set @strSQL = 'SELECT * FROM (select top ' + str(@PageSize) +' '+@Select_List+ '  from [' + @TableName + '] where ' + @SqlWhere + ' ' + @strOrder + ') AS TempTab ORDER BY '+ @TaxisFielesName + ' ' + @strTaxis
     else
       set @strSQL = 'SELECT * FROM (select top ' + str(@PageSize) +' '+@Select_List+ '  from ['+ @TableName + '] '+ @strOrder + ') AS TempTab ORDER BY '+ @TaxisFielesName + ' ' + @strTaxis
    END
  ELSE
    BEGIN
       set @strSQL = 'SELECT * FROM (select top ' + str(@PageSize) +' '+@Select_List+ '  from ['
          + @TableName + '] where [' + @KeyFieldsName + ']' + @strTmp + '(['+ @KeyFieldsName + ']) from (select top ' + str((@CurrentPage-1)*@PageSize) + ' ['+ @KeyFieldsName + '] from [' + @TableName + ']' + @strOrder + ') as tblTmp)'+ @strOrder + ') AS TempTab ORDER BY '+ @TaxisFielesName + ' ' + @strTaxis
      
       if @SqlWhere != ''
     set @strSQL = 'SELECT * FROM (select top ' + str(@PageSize) +' '+@Select_List+ '  from ['
   + @TableName + '] where [' + @KeyFieldsName + ']' + @strTmp + '(['
   + @KeyFieldsName + ']) from (select top ' + str((@CurrentPage-1)*@PageSize) + ' ['
   + @KeyFieldsName + '] from [' + @TableName + '] where ' + @SqlWhere + ' '
   + @strOrder + ') as tblTmp) and ' + @SqlWhere + ' ' + @strOrder+ ') AS TempTab ORDER BY '+ @TaxisFielesName + ' ' + @strTaxis

    END
END
exec sp_executesql @strRunRowSQL,N'@iCount int output',@iCount output
set @RowCount =@iCount
exec (@strSQL)