SQL 2005 存储过程分页

create PROCEDURE [dbo].[P_PageTest]
    @SQL Nvarchar(max),  --SQL语句不包括排序
    @CurPage int,    --当前页
    @PageRows int,    --页面尺寸
 @Order Nvarchar(20),  --排序字段
 @OrderType Nvarchar(10), --排序类型倒序desc或正序asc
    @TotalRecorder int output
AS
BEGIN
    SET NOCOUNT ON;
    declare @ExceSQL nvarchar(4000)
    --设置开始行号
    declare  @start_row_num AS int
 declare  @end_row_num AS int
 if(@CurPage=1)
  begin
  SET @start_row_num = (@CurPage - 1) * @PageRows
  SET @end_row_num = @start_row_num+@PageRows
  end
 else
  begin
  SET @start_row_num = ((@CurPage - 1) * @PageRows)+1
  SET @end_row_num = (@start_row_num+@PageRows)-1
  end
    --设置表示
    declare @RowNumber nvarchar(100)
    set @RowNumber = ', ROW_NUMBER() OVER(ORDER BY ' + @Order +' '+ @OrderType + ') as RowNumber from '
    set @SQL = Replace(@SQL,' from ',@RowNumber)
    --获得总记录数
    set @ExceSQL = 'WITH tmp AS (' + @SQL + ')
        select @TotalRecorder=max(RowNumber) from tmp'
    execute sp_executesql @ExceSQL,N'@TotalRecorder int output',@TotalRecorder output
    --查询语句
    set @ExceSQL = 'WITH tmp AS (' + @SQL + ')
        select * from tmp where RowNumber between ' + Convert(nvarchar,@start_row_num)
        + ' And ' + Convert(nvarchar,@end_row_num)
    execute(@ExceSQL)
END