sqlserver2005 存储过程分页

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[RecvSMSLog_queryPage]

@UcNum nvarchar(20),           --电话号码
@PageSize int,                    --每页多少条记录
@pageIndex int = 1 ,              --指定当前为第几页
@TotalPage int  output           --返回总页数

 /*
 (
 @parameter1 int = 5,
 @parameter2 datatype OUTPUT
 ) 分页查询 接收信息
 */
AS
 
BEGIN
 Begin tran -- 开始事务
 declare @sql nvarchar(3000)
 declare @totalRecord int --总记录数
 
 --计算总记录数  
    set @sql = 'select @totalRecord = count(id) from (select * from dbo.RecvSMSLog) As tmpTable'
 
 EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录
 
  --计算总页数

  select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)   
 
    --处理页数超出范围情况
    if @PageIndex<=0
        Set @pageIndex = 1
   
    if @pageIndex>@TotalPage
        Set @pageIndex = @TotalPage

     --处理开始点
    Declare @StartRecord int
   
    set @StartRecord = (@pageIndex-1)*@PageSize
   
 if @pageIndex = 1
  begin
   Set @sql = 'select top ' + Convert(varchar(50),@PageSize) + ' * from dbo.RecvSMSLog '
   
   if len('' + @UcNum) > 0
   begin
    Set @sql = @sql + ' where UcNum = ''' + @UcNum + ''''
   end
   
   Set @sql = @sql + ' order by id desc'
  end
 else
 begin
   
   
    Set @sql = 'select top ' + Convert(varchar(50),@PageSize) + ' * from RecvSMSLog WHERE  (ID < (SELECT MIN(ID) AS Expr1 FROM  (SELECT TOP  ' + Convert(varchar(50),@StartRecord) + ' ID FROM RecvSMSLog AS RecvSMSLog_1 ORDER BY ID desc) AS derivedtbl_1))'
  
  if len('' + @UcNum) > 0
   begin
    Set @sql = @sql + ' and (UcNum = ''' + @UcNum + ''')'
   end
   
   Set @sql = @sql + ' order by id desc'
    end

 print @sql
    Exec(@sql)
  
    ---------------------------------------------------
    If @@Error <> 0
      Begin
        RollBack Tran
        Return -1
      End
     Else
      Begin
        Commit Tran
        ---返回记录总数
        Return @totalRecord
      End  

END