09 Feb 2004

分页存储过程

Share

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

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

    实现功能:   分页显示当用户选择了图片大分类时(按手机)的数据
    参数说明:[email protected]
[email protected]        
        $PageSize为每页显示的记录数量
[email protected]             
    LastUpdate:            
    ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
CREATE procedure sms_img_page_no_smalltype
(
[email protected] nvarchar(20),
[email protected] int,
[email protected] int,
[email protected] 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)[email protected]
  set @PageUpperBound= @[email protected]
  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
[email protected]
    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<
[email protected]
   and
[email protected]
   and q.col_mobile_comment like @LikeBigType
 
 order by p.id

set nocount off
GO

comments powered by Disqus