分页存储过程一

/*********************************************************************************
*      Function:  GetPageList              *
*      Description:                                                              *
*             Sql2008分页存储过程             *
*      Author:                                                                   *
*             SUNSHICHENG                     *
*      Finish DateTime:                                                          *
*             2011/10/19               *
*    Example:                  *
*           GetPageData @Tablename = 'Table1', @Returnfields = 'ID',           *
*     @PageIndex = 0, @PageSize = 10, @Where = '', @Orderfld = 'ID',     *
*     @Ordertype = 0              *          
*********************************************************************************/
ALTER PROCEDURE [dbo].[Pro_GetPageList]
(
    @TableName  nvarchar(3000),   -- 表名
 @ReturnFields nvarchar(3000) = '*', -- 需要返回的列
 @PageSize  int = 10,    -- 每页记录数
 @PageIndex  int = 0,    -- 当前页码
 @Where   nvarchar(3000) = '',    -- 查询条件
 @Orderfld  nvarchar(200),   -- 排序字段名 最好为唯一主键
 @OrderType  int = 1     -- 排序类型 1:降序 其它为升序
)
AS
 DECLARE @TotalRecord int
 DECLARE @TotalPage int
 DECLARE @StartPageSize int
    DECLARE @EndPageIndex int
    DECLARE @OrderBy nvarchar(255)
    DECLARE @CountSql nvarchar(4000) 
    DECLARE @Sql nvarchar(4000)
   
    if @OrderType = 1
  BEGIN
   set @OrderBy = ' Order by ' + REPLACE(@Orderfld,',',' desc,') + ' desc '
  END
 else
  BEGIN
   set @OrderBy = ' Order by ' + REPLACE(@Orderfld,',',' asc,') + ' asc '  
  END
 
 -- 总记录
 set @CountSql='SELECT @TotalRecord=Count(*) From ('+@TableName+') as t '+@Where
 execute sp_executesql @CountSql,N'@TotalRecord int out',@TotalRecord out
 
 SET @TotalPage=(@TotalRecord-1)/@PageSize+1
 SET @StartPageSize=(@PageIndex-1)*@PageSize
 set @EndPageIndex=@PageIndex*@PageSize
 
 set @Sql='SELECT ROW_NUMBER() OVER ('+ @OrderBy +') AS ROWNUM, '+@ReturnFields+' FROM ('+@TableName+') t '+@Where
 
 EXEC('SELECT * FROM ('+ @Sql + ') AS TempTable WHERE TempTable.ROWNUM > '+@StartPageSize+'  and TempTable.ROWNUM <= '+@EndPageIndex+' ')
 
 -- 返回总页数和总记录
 SELECT @TotalPage as PageCount,@TotalRecord as RecordCount