一个比较通用的分页存储过程

闲着没事，想起了之前写的一个存储过程。以前在做项目的过程中总是在那里使劲的想我这个存储过程写过了的，可到底放哪里了呢(想不到了，只好自己又重新写一遍)，唉！
今天将它摘录下来，以后就不用这么的麻烦了。。。。。。
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[UP_GetRecordByPage]  
@TotalRecord int output ,            --返回总记录数
@TotalPage int output ,                --返回总页数
@tblName varchar(500),                --表名       
@fldName varchar(5000) = '*',        --字段名(全部字段为*)       
@OrderFiled varchar(5000),            --排序字段(必须!支持多字段) 
@OrderType int = 0,                    --设置排序类型, 非 0 值则降序     
@strWhere varchar(5000) = Null,        --条件语句(不用加where)       
@PageSize int= 10,                  --每页多少条记录       
@PageIndex int = 1 ,                --指定当前为第几页       
@MaxReturn int =2000     

As       
begin       
    Begin Tran --开始事务       
       
    Declare @sql nvarchar(4000);       
    Declare @totalRe int;
    Declare @strOrder  varchar(400);         -- 排序类型         
    DECLARE @TempSql nvarchar(4000);      
       
    --计算总记录数       
                
    if (@strWhere='' or @strWhere=null)       
        set @sql = 'select @totalRe = count(*) from ' + @tblName       
    else       
        set @sql = 'select @totalRe = count(*) from ' + @tblName + ' where ' + @strWhere       
       
    EXEC sp_executesql @sql,N'@totalRe int OUTPUT',@totalRe OUTPUT--计算总记录数               
           
    --计算总页数       
    select @TotalPage=CEILING((@totalRe+0.0)/@PageSize)       

    if @OrderType != 0
    begin
        set @strOrder = ' order by ' + @OrderFiled +' desc '
    end
    else
    begin
        set @strOrder = ' order by ' + @OrderFiled +' asc '
    end
 
    --记录数       
    select @TotalRecord=@totalRe       
    if (@strWhere='' or @strWhere=null)       
       SET @TempSql =' Select TOP '+ Convert(varchar(50),@MaxReturn)  +' '+ @fldName +' FROM '+ @tblName + @strOrder    
    else     
        SET @TempSql =' Select TOP '+ Convert(varchar(50),@MaxReturn)  +' '+ @fldName +' FROM '+ @tblName+ ' where ' + @strWhere + @strOrder             
    SET @sql =' WITH temptbl AS     
         (     
             SELECT  ROW_NUMBER() Over(' + @strOrder + ' ) as RowNumber,a.* FROM ('+@TempSql+') as a     
         )     
        '               
    --处理页数超出范围情况       
    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 +'  SELECT * FROM temptbl WHERE RowNumber BETWEEN ' + Convert(varchar(50),@StartRecord) + ' and '+ Convert(varchar(50),@EndRecord)
        
    Exec(@Sql)       
    ---------------------------------------------------       
    If @@Error <> 0       
      Begin       
        RollBack Tran       
        Return -1       
      End       
     Else       
      Begin       
        Commit Tran       
        Return @TotalRecord ---返回记录总数       
      End           
end
--------------------------------------------------------------------------------------------------------------------
然后需要做的就是写个方法调用上面的存储过程:
public DataSet GetClienteleFeYePro(string tableName, string fldName, string orderFiled, int OrderType, string strWhere, int pageSize, int pageIndex, out int allTotal,out int totalPage)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("UP_GetRecordByPageTest");
            db.AddInParameter(dbCommand, "tblName", DbType.AnsiString, tableName);
            db.AddInParameter(dbCommand, "fldName", DbType.AnsiString, fldName);
            db.AddInParameter(dbCommand, "OrderFiled", DbType.AnsiString, orderFiled);
            db.AddInParameter(dbCommand, "OrderType", DbType.Int32, OrderType);
            db.AddInParameter(dbCommand, "strWhere", DbType.String, strWhere);
            db.AddInParameter(dbCommand, "PageSize", DbType.Int32, pageSize);
            db.AddInParameter(dbCommand, "PageIndex", DbType.Int32, pageIndex);
            db.AddOutParameter(dbCommand, "TotalRecord", DbType.Int32, 4);
            db.AddOutParameter(dbCommand, "TotalPage", DbType.Int32, 4);
            var ds = db.ExecuteDataSet(dbCommand);
            allTotal = (int)db.GetParameterValue(dbCommand, "TotalRecord");//获取存储过程中的返回总记录参数
            totalPage = (int)db.GetParameterValue(dbCommand, "TotalPage");//获取存储过程中的返回总页数参数
            return ds;
        }