sql 2005 分页的存储过程 及调用实例

/* 创建存诸过程 */
create procedure procPageList
 @CurrentPage int,
 @PageSize int,
 @Field_Info varchar(500),
@Table_Info varchar(20),
 @Field_ID varchar(10),
@Field_Order varchar(100),
@OtherWhere varchar(50),
@RecordCount int output,
@PageCount int output
/*名称:procPageList 功能:分页
显示参数: @CurrentPage为显示那一页,
 @PageSize为每一页显示几行,
 @Field_Info为要显示的字段可以为*,
 @Table_Info为要查询的表或视图,
 @Field_ID 主键或唯一字段,
 @Field_Order 排序字段,
 @OtherWhere为条件,
 @RecordCount为总行数,
 @PageCount为总页数*/
 as
 begin
 declare @MinPage int, @MaxPage int,@sql varchar(1000),@sqlt nvarchar(300)
  set @sqlt='select @RecordCount=count('+@Field_ID+') from '+@Table_Info+' where '+@OtherWhere
   exec sp_executesql @sqlt,N'@RecordCount int output',@RecordCount output   
--将exec执行结果放入变量中,如果是字符串就要用N,N后面的变量一定要和@sqlt里面的变量同名
if @PageSize<=0
 begin   
    set @PageSize=10   
     end else if @PageSize>@RecordCount
          begin    
     set @pageSize=@RecordCount 
    end
set @pagecount=@RecordCount/@PageSize
if ((@recordcount%@pagesize)!=0)--如果除不净则加一页 
    begin 
         set @PageCount=@RecordCount/@PageSize   
      set @PageCount=@pagecount+1    
       end else
           begin   
              set @pagecount=@recordcount/@PageSize
                end
if @CurrentPage<=0    
   begin     
    set @CurrentPage=1     
     end else if @CurrentPage>@pagecount
     begin    
          set @currentpage=@pagecount    --如果输入页数大于总页数则符最后一页
      end
set @MinPage=(@CurrentPage-1)*@PageSize+1
  set @MaxPage=@MinPage+@PageSize-1
begin    
    if @Field_Info like ''  
       set @field_Info = '*'   
         if @otherwhere like ''  
          set @sql = 'select ' + @Field_Info + ' from (select ' +  @Field_Info + ' , row_number() over(order by ' + @Field_Order + ') as rownumber from ' + @Table_info + ') as a where rownumber between ' + convert(varchar(10),@minpage) + ' and ' + convert(varchar(10),@maxpage)    
          else 
           set @sql = 'select ' + @Field_Info + ' from (select ' +  @Field_Info + ' , row_number() over(order by ' + @Field_Order + ') as rownumber from ' + @Table_info + ') as a where rownumber between ' + convert(varchar(10),@minpage) + ' and ' + convert(varchar(10),@maxpage) + ' and ' + @otherwhere 
           exec(@sql)  
        end
end


go

/* 例子*/

/****** 对象:  Table [dbo].[page]    脚本日期: 09/27/2010 16:15:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[page](
    [pagid] [bigint] IDENTITY(1,1) NOT NULL,
    [title] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [updt] [datetime] NULL,
    [ordern] [bigint] NULL,
    [des] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 CONSTRAINT [PK_page] PRIMARY KEY CLUSTERED
(
    [pagid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
/* 添加数据 */

go

 declare @i int
 set @i = 0
  while  @i<100000
  begin
    insert into page (title,updt,ordern,des) values(newid(),getdate(),5258888,'indes'+cast(@i as nvarchar(20)))
   set @i = @i+1
  end

go

/* 调用分页的存储程 */
declare @pc int,@c int
exec procPageList 101,10,'*','page','pagid','updt','1=1',@pc output,@c output
select @pc ,@c