sql server2005 rownum通用存储过程分页

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER   PROCEDURE [dbo].[sp_GetRecordFromPage]
 @TableName varchar(350),        --表名
 @Fields varchar(5000) = "*",    --字段名(全部字段为*)
 @OrderField varchar(5000),        --排序字段(必须!支持多字段)
 @sqlWhere varchar(5000) = Null,--条件语句(不用加where)
 @pageSize int,                    --每页多少条记录
 @pageIndex int = 1,         --指定当前为第几页
 @distinct VARCHAR(50)=NULL,   --去除重复值，注意只能是一个字段
 @top INT=NULL                --查询TOP,不传为全部
AS 
BEGIN
    Declare @sql nvarchar(4000);
    Declare @totalRecord int;    
    DECLARE @totalPage INT;
    --计算总记录数
 IF (@distinct IS NULL OR @distinct="")
 BEGIN
  IF  (@SqlWhere="" OR  @sqlWhere IS NULL)
    SET  @sql = "select @totalRecord = count(1) from " + @TableName
   ELSE
    SET @sql = "select @totalRecord = count(1) from " + @TableName + " where " + @sqlWhere
 END
 ELSE
 BEGIN
  IF  (@SqlWhere="" OR  @sqlWhere IS NULL)
    SET  @sql = "select @totalRecord = count(distinct " + @distinct + ") from " + @TableName
   ELSE
    SET @sql = "select @totalRecord = count(distinct " + @distinct + ") from " + @TableName + " where " + @sqlWhere
 END
    EXEC sp_executesql @sql,N"@totalRecord int OUTPUT",@totalRecord OUTPUT--计算总记录数       
    
  
    IF(@top>0)
    BEGIN
  --指定TOP 记录
  SET @Fields= "top " + CAST(@top AS VARCHAR(20)) + " " +  @Fields;
  --如果总记录数超过TOP数,设总记录数为TOP数
  IF(@totalRecord>@top) 
   SET @totalRecord=@top
 END
  
    --计算总页数
    SELECT @totalPage=CEILING((@totalRecord+0.0)/@PageSize)
    SELECT @totalRecord AS "fldtotalRecord",@totalPage AS "fldTotalPage"
 
 IF (@distinct IS NULL OR @distinct="")
 BEGIN
  IF (@SqlWhere="" or @sqlWhere IS NULL)
   SET @sql = "Select * FROM (select " + @Fields + ",ROW_NUMBER() Over(order by " + @OrderField + ") as rowId from " + @TableName
  ELSE
   SET @sql = "Select * FROM (select " + @Fields + ",ROW_NUMBER() Over(order by " + @OrderField + ") as rowId from " + @TableName + " where " + @SqlWhere    
 END
 ELSE
 BEGIN
  IF (@SqlWhere="" or @sqlWhere IS NULL)
   SET @sql = "Select * FROM (select " + @Fields + ",ROW_NUMBER() Over(order by " + @OrderField + ") as rowId from " + @TableName
  ELSE
   SET @sql = "Select * FROM (select " + @Fields + ",ROW_NUMBER() Over(order by " + @OrderField + ") as rowId from " + @TableName + " where " + @SqlWhere    
  SET @sql=@sql + " GROUP BY " + @distinct;
 END
    
     
    --处理页数超出范围情况

    IF @PageIndex<=0 
        SET @pageIndex = 1
    
   
 
     --处理开始点和结束点
    DECLARE @StartRecord INT 
    DECLARE @EndRecord int
    
    SET @StartRecord = (@pageIndex-1)*@PageSize + 1
    SET @EndRecord = @StartRecord + @pageSize - 1
 
     --继续合成sql语句
    SET @sql = @sql + ") as tempTable where rowId >=" + CONVERT(VARCHAR(50),@StartRecord) + " and rowid<= " +  CONVERT(VARCHAR(50),@EndRecord)
    Exec(@sql)
end