文章有二个实例一个是sql2000的分页存储过程一个是sql2005的分页存储过程，有需要的同学可以拿去看看。
 代码如下 复制代码
create procedure [dbo].[proccustompage]
    (
     @table_name varchar(5000),    --表名
     @sign_record varchar(50),     --主键
     @filter_condition varchar(1000),    --筛选条件,不带where
     @page_size int,     --页大小
     @page_index int,       --页索引      
   @taxisfield varchar(1000), --排序字段
     @taxis_sign int,     --排序方式 1为 desc, 0为 asc
@find_recordlist varchar(1000),   --查找的字段
     @record_count int     --总记录数
     )
     as
      begin
      declare @start_number int
      declare @end_number int
      declare @topn_number int
     declare @ssql varchar(8000)
if(@find_recordlist='')
begin
select @find_recordlist='*'
end
     select @start_number =(@page_index-1) * @page_size
      if @start_number     select @start_number=0
      select @end_number=@start_number+@page_size
      if @end_number>@record_count
     select @end_number=@record_count
     select @topn_number=@end_number-@start_number
     if @topn_number     select @topn_number=0
      print @topn_number
     print @start_number
     print @end_number
     print @record_count
if @taxisfield=''
begin
@taxisfield=@sign_record
end
     if @taxis_sign=0
       begin
         if @filter_condition=''
         begin
           select @ssql='select '+@find_recordlist+' from '+@table_name+'
           where '+@sign_record+' in (select top '+cast(@topn_number as varchar(10))+' '+@sign_record+' from '+@table_name+'
            where '+@sign_record+' in (select top '+cast(@end_number as varchar(10))+' '+@sign_record+' from '+@table_name+'
          order by '+@taxisfield+') order by '+@taxisfield+' desc)order by '+@taxisfield
         end
        else
        begin
        select @ssql='select '+@find_recordlist+' from '+@table_name+'
     where '+@sign_record+' in (select top '+cast(@topn_number as varchar(10))+' '+@sign_record+' from '+@table_name+'
     where '+@sign_record+' in (select top '+cast(@end_number as varchar(10))+' '+@sign_record+' from '+@table_name+'
     where '+@filter_condition+' order by '+@taxisfield+') and '+@filter_condition+' order by '+@taxisfield+' desc) and '+@filter_condition+' order by '+@taxisfield
         end
      end
    else
      begin
      if @filter_condition=''
        begin
          select @ssql='select '+@find_recordlist+' from '+@table_name+'
     where '+@sign_record+' in (select top '+cast(@topn_number as varchar(10))+' '+@sign_record+' from '+@table_name+'
     where '+@sign_record+' in (select top '+cast(@end_number as varchar(10))+' '+@sign_record+' from '+@table_name+'
     order by '+@taxisfield+' desc) order by '+@taxisfield+')order by '+@taxisfield+' desc'
     end
      else
      begin
        select @ssql='select '+@find_recordlist+' from '+@table_name+'
     where '+@sign_record+' in (select top '+cast(@topn_number as varchar(10))+' '+@sign_record+' from '+@table_name+'
     where '+@sign_record+' in (select top '+cast(@end_number as varchar(10))+' '+@sign_record+' from '+@table_name+'
     where '+@filter_condition+' order by '+@taxisfield+' desc) and '+@filter_condition+' order by '+@taxisfield+') and '+@filter_condition+' order by '+@taxisfield+' desc'
     end
      end
      exec (@ssql)
      if @@error0
      return -3
     return 0
     end
print @ssql
go
sql2005
 代码如下 复制代码
create procedure [dbo].[getrecordfrompage]
@selectlist varchar(2000), --欲选择字段列表
@tablesource varchar(100), --表名或视图表
@searchcondition varchar(2000), --查询条件
@orderexpression varchar(1000), --排序表达式
@pageindex int = 1, --页号,从0开始
@pagesize int = 10 --页尺寸
as
begin
if @selectlist is null or ltrim(rtrim(@selectlist)) = ''
begin
set @selectlist = '*'
end
print @selectlist
set @searchcondition = isnull(@searchcondition,'')
set @searchcondition = ltrim(rtrim(@searchcondition))
if @searchcondition ''
begin
if upper(substring(@searchcondition,1,5)) 'where'
begin
set @searchcondition = 'where ' + @searchcondition
end
end
print @searchcondition
set @orderexpression = isnull(@orderexpression,'')
set @orderexpression = ltrim(rtrim(@orderexpression))
if @orderexpression ''
begin
if upper(substring(@orderexpression,1,5)) 'where'
begin
set @orderexpression = 'order by ' + @orderexpression
end
end
print @orderexpression
if @pageindex is null or @pageindex begin
set @pageindex = 1
end
print @pageindex
if @pagesize is null or @pagesize begin
set @pagesize = 10
end
print @pagesize
declare @sqlquery varchar(4000)
set @sqlquery='select '+@selectlist+',rownumber
from
(select ' + @selectlist + ',row_number() over( '+ @orderexpression +') as rownumber
from '+@tablesource+' '+ @searchcondition +') as rownumbertablesource
where rownumber between ' + cast(((@pageindex - 1)* @pagesize+1) as varchar)
+ ' and ' +
cast((@pageindex * @pagesize) as varchar)
-- order by ' + @orderexpression
print @sqlquery
set nocount on
execute(@sqlquery)
set nocount off
return @@rowcount
end