09 Feb 2004

分页存储过程

搞了一中午终于可以用了 但不知道效率如何.郁闷的是我要用php去调用它

  /*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    CopyRight:   ******
    CreateData:   2004-02-09
    Language:   MS SQL SERVER 存储过程
    AuthorName:  Meibo Wu

    实现功能:   分页显示当用户选择了图片大分类时(按手机)的数据
    参数说明:   @bigtype为大分类
    @gid为传入的图片类型           
        $PageSize为每页显示的记录数量
    @pageindex为当前页码                             
    LastUpdate:            
    ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
CREATE procedure sms_img_page_no_smalltype
(
 @bigtype nvarchar(20),
 @gid int,
 @pagesize int,
 @pageindex int
)
as
set nocount on
 declare @LikeBigType nvarchar(20)
 declare @PageLowerBound int
 declare @PageUpperBound int
 declare @indextable table(id int identity(1,1),nid int)
  set @PageLowerBound=(@pageindex-1)*@pagesize
  set @PageUpperBound= @PageLowerBound+@pagesize
  set @LikeBigType= @bigtype+'%'
  set rowcount @PageUpperBound
 insert into @indextable(nid)
  select  a.col_id
   from web_sms_data_img as a join web_sms_data_mobile as q
   on a.col_mobile_type=q.col_mobile_type
   where
a.col_img_group=@gid
    and q.col_mobile_comment  like @LikeBigType
   order by a.col_id desc 
 /*
 select  @LikeBigType
 select * from @indextable 
 */
 select q.col_mobile_comment,
  a.col_id,
  a.col_img_hits,
  a.col_img_dir,
  a.col_img_name
  from web_sms_data_img as a join web_sms_data_mobile as q
   on a.col_mobile_type=q.col_mobile_type
    join @indextable  as p
    on a.col_id=p.nid
  where p.id>@PageLowerBound and p.id<
=@PageUpperBound
   and
a.col_img_group=@gid
   and q.col_mobile_comment like @LikeBigType
 
 order by p.id

set nocount off
GO