Sql2005中分页存储过程

SET ANSI_NULLS ON
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

alter PROCEDURE PageTest
 @tblName      varchar(255),       -- 表名
    @fldName      varchar(255),       -- 主键字段名
    @PageSize     int = 10,           -- 页尺寸
    @PageIndex    int = 1,            -- 页码
    @OrderType    bit = 1,            -- 设置排序类型, 非 0 值则降序
    @strWhere     varchar(1000) = '', -- 查询条件 (注意: 不要加 where)
    @PageCount int = 1 output,    -- 总页数
    @RecordCount int = 1 output       --总条数 
AS
 declare @strSQL   nvarchar(4000)       -- 主语句
 declare @strTmp   nvarchar(4000)       -- 临时变量
 declare @strOrder nvarchar(400)        -- 排序类型
 declare @strCount nvarchar(4000)       -- 统计条数
 declare @PageSizeStart int
 declare @PageSizeEnd int

if @OrderType != 0
begin
 set @strOrder =' order by '+@fldName+' desc '
end
else
begin
 set  @strOrder =' order by '+@fldName+' asc '
end

if @strWhere !=''
begin
 if str(@PageIndex) = 1
  set @strSQL = 'select top '+str(@PageSize)+' * from '+@tblName+' where '+@strWhere +''+@strOrder
 else
  begin
   set @PageSizeStart = (@PageIndex -1)*@PageSize+1
   set @PageSizeEnd = @PageIndex*@PageSize
   set @strSQL = 'select * from (select *, ROW_NUMBER() OVER('+@strOrder+') as row from
      '+@tblName+' where '+@strWhere+'  ) as A where row between '+@PageSizeStart+' and '+@PageSizeEnd
  end
end

else
begin
 if str(@PageIndex) = 1
  begin
   set @strSQL = 'select top '+str(@PageSize)+' * from '+@tblName+''+@strOrder
  end
 else
  begin
  set @PageSizeStart = (@PageIndex -1)*@PageSize+1
  set @PageSizeEnd = @PageIndex*@PageSize
  set @strSQL = 'select * from (select *, ROW_NUMBER() OVER('+@strOrder+') as row from
   '+@tblName+') as A where row between '+str(@PageSizeStart)+' and '+str(@PageSizeEnd)+''
  end
end  
------取得查询结果总数量-----
if @strWhere != ''
set @strCount = 'select @RecordCount = count(*) from [' + @tblName + ']'+' where ' + @strWhere
else
set @strCount = 'select @RecordCount = count(*) from [' + @tblName + ']'
exec sp_executesql @strCount,N'@RecordCount int out',@RecordCount out

--取得分页总数
if @RecordCount <= @PageSize 
 set @PageCount = 1
else
 set @PageCount = (@RecordCount-1)/@PageSize +1
print(@strSQL)
exec(@strSQL)