存储过程分页

CREATE proc w_Log_select

@LogType int,
@rowsCount int output,--符合条件的记录
@pageSize int output, --每页要显示记录数
@pageCount int output,--页码
@pageIndex int output --第几页

declare @sql varchar(1000)
set @pageSize =15
set @pageIndex = isnull(@pageIndex, 1)---默认第一页

begin

select @rowsCount=count(LogId) from A_Log where LogType=@LogType
if @rowsCount = 0
   begin
     set @pageCount = 0
     return
   end
   set @pageCount=case @RowsCount % @pageSize when 0 then @RowsCount / @pageSize else @RowsCount / @pageSize + 1 end

if @pageIndex > @pageCount
set @pageIndex = @pageCount

if @pageIndex=1

begin

set @sql = 'select top ' + cast(@pageSize as varchar(100)) + ' LogId,LogType,LogText,LogTime'
              + ' from A_Log'
              + ' where LogType = ' + convert(varchar(5), @LogType)
              + ' order by LogId desc'

end

else

    begin

    set @sql= 'select top ' + convert(varchar(10), @pageSize) + ' LogId, LogType, LogText, LogTime'
              + ' from A_Log'
              + ' where LogType = ' + convert(varchar(5), @LogType)
              + '   and LogId < '
              + '      (select min(LogId)'
              + '         from ('
              + '              select top ' + convert(varchar(10), (@pageIndex - 1) * @pageSize) + ' LogId'
              + '                from A_Log'
              + '               where LogType =' + convert(varchar(5), @LogType)
              + '               order by LogId desc'
              + '              ) as a'
              + '       )'
              + ' order by LogId desc'

    end

end

print(@sql)
exec (@sql)
GO

徐燕平