在讯飞开发的搜索分页存储过程

USE [aheduns_bnsrjy]
GO
/****** Object:  StoredProcedure [dbo].[P_GETSEARCHDATAFORLIST]    Script Date: 12/20/2009 17:00:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/***************************************************

  developer:stephen-周
  develope-date:2009-12-15
  function:百年树人资源搜索
  @flag ------0 非关键字搜索 1关键字搜索

****************************************************/
ALTER proc [dbo].[P_GETSEARCHDATAFORLIST] --'tk000036',10,20,'英语',0
(
 @treeNodeID varchar(50),
 @firstNo int,
    @lastNo int,
 @keyword varchar(100) = null,
 @flag bit,
 @returnValue int = -1 out
)

as

if @firstNo is null or @lastNo is null or @firstNo=0 or @lastNo =0
 begin
   select  @returnValue = 0 ---页面参数值非法
 end
else
 begin
   declare @sql varchar(1000)   --sql
   declare @pageCount int    --取的记录数目
   declare @pageNumber int    --运算产生的页数
   declare @pageTotalCount int   --最后一页的数目
   declare @prefix varchar(10)   --前缀
   declare @searchField varchar (50) --检索匹配字段
   declare @searchTotalCount int  --搜索的总数
   
   set @pageCount = @lastNo - @firstNo  + 1 --5
   set @pageNumber = @lastNo / @pageCount   --25/5=5
   --set @pageTotalCount = (@pageNumber-1)*@pageCount --正序计算方式
   set @pageTotalCount = @pageNumber*@pageCount   --倒序计算方式   25
   set @prefix = left(@treeNodeID,2)--前缀匹配检索方式
   
   
   --按教材搜索
   if(@prefix = 'no' or @prefix = 'tk')
    begin    
     set @searchField = 'classification_taxon_path' 
     if(@flag = 0)
      select @searchTotalCount = count(*) from  tresource where public_tosee=2 and classification_taxon_path like '%'+ @treeNodeID+'%'
     else
      select @searchTotalCount = count(*) from  tresource where public_tosee=2 and classification_taxon_path like '%'+ @treeNodeID+'%' and general_title like '%'+@keyword+'%' or general_description like '%'+@keyword+'%'
    end
   --按课标搜索
   if(@prefix = 'ep')
    begin
     set @searchField = 'EDU_DESCRIPTION'
     if(@flag = 0)
      select @searchTotalCount = count(*) from  tresource where public_tosee=2 and EDU_DESCRIPTION like '%'+ @treeNodeID+'%'
     else
      select @searchTotalCount = count(*) from  tresource where public_tosee=2 and EDU_DESCRIPTION like '%'+ @treeNodeID+'%' and general_title like '%'+@keyword+'%' or general_description like '%'+@keyword+'%'
    end
   --按年级搜索
   if(@prefix = 'mg')
    begin
     set @searchField = 'edu_typical_learning_age'
     if(@flag = 0)
      select @searchTotalCount = count(*) from  tresource where public_tosee=2 and edu_typical_learning_age like '%'+ @treeNodeID+'%'
     else
      select @searchTotalCount = count(*) from  tresource where public_tosee=2 and edu_typical_learning_age like '%'+ @treeNodeID+'%' and general_title like '%'+@keyword+'%' or general_description like '%'+@keyword+'%'
    end
   --按学科搜索
   if(@prefix = 'ms')
    begin
     set @searchField = 'edu_learning_resource_type'
     if(@flag = 0)
      select @searchTotalCount = count(*) from  tresource where public_tosee=2 and edu_learning_resource_type like '%'+ @treeNodeID+'%'
     else
      select @searchTotalCount = count(*) from  tresource where public_tosee=2 and edu_learning_resource_type like '%'+ @treeNodeID+'%' and general_title like '%'+@keyword+'%' or general_description like '%'+@keyword+'%'
    end
   --按媒体搜索
   if(@prefix = 'mf')
    begin
     set @searchField = 'technical_format'
     if(@flag = 0)
      select @searchTotalCount = count(*) from  tresource where public_tosee=2 and technical_format like '%'+ @treeNodeID+'%'
     else
      select @searchTotalCount = count(*) from  tresource where public_tosee=2 and technical_format like '%'+ @treeNodeID+'%' and general_title like '%'+@keyword+'%' or general_description like '%'+@keyword+'%'
    end
   --按专题搜索
   if(@prefix = 'es')
    begin
     set @searchField = 'EDU_LANGUAGE'
     if(@flag = 0)
      select @searchTotalCount = count(*) from  tresource where public_tosee=2 and EDU_LANGUAGE like '%'+ @treeNodeID+'%'
     else
      select @searchTotalCount = count(*) from  tresource where public_tosee=2 and EDU_LANGUAGE like '%'+ @treeNodeID+'%' and general_title like '%'+@keyword+'%' or general_description like '%'+@keyword+'%'
    end

   if(@searchTotalCount<@lastNo and @searchTotalCount>@firstNo)
    begin
     set @pageCount = @searchTotalCount - @firstNo +1
    end
   if(@searchTotalCount<@firstNo)
    begin
     set @pageCount = 0
    end

   if(@flag = 0) --非关键字搜索
    begin
     set @sql='select top '+convert(varchar,@pageCount)+' y.* from (select * from tresource '+
     'where '+
     ''+@searchField+' like ''%'+ @treeNodeID+'%''  and general_id >='+
     '(select isnull(min(general_id),0) from '+
      '('+
      'select top '+convert(varchar,@pageTotalCount)+' general_id from tresource '+
      'where '+@searchField+' like ''%'+ @treeNodeID+'%'' and public_tosee =2 order by general_id desc'+
      ') x'+
     ') and public_tosee =2) y '+
     'order by y.general_id'
     
     --exec P_WritefileLog @sql  --日志记录查看
     exec(@sql)
     set @sql='select count(*) from tresource where public_tosee =2 and '+@searchField+' like ''%'+ @treeNodeID+'%'''
     --exec P_WritefileLog @sql  --日志记录查看
     exec(@sql)
    end

   if(@flag = 1) --关键字搜索
    begin
     /*---------按general_id正序排列的sql----------*/
     /*set @sql='select top '+convert(varchar,@pageCount)+' * from (select * from tresource '+
     'where '+
     'general_title like ''%'+@keyword+'%'' or general_description like ''%'+@keyword+'%'' and '+@searchField+' like ''%'+ @treeNodeID+'%'') x  where  x.general_id >='+
     '(select isnull(min(general_id),0) from '+
      '('+
      'select top '+convert(varchar,@pageTotalCount)+' general_id from tresource '+
      'where general_title like ''%'+@keyword+'%'' or general_description like ''%'+@keyword+'%'' and '+@searchField+' like ''%'+ @treeNodeID+'%'' order by general_id desc'+
      ') xx'+
     ')) y '+
     'order by general_id'*/
     /*---------按general_id倒序排列的sql----------*/
     set @sql ='select top '+convert(varchar,@pageCount)+' y.* from
        (
          select * from
          (
           select * from tresource x where public_tosee =2 and '+@searchField+' like ''%'+@treeNodeID+'%'' and general_title  like ''%'+@keyword+'%'' or general_description like ''%'+@keyword+'%''
          ) x
          where  x.general_id >=
          (
           select isnull(min(general_id),0) from
           (
            select top '+convert(varchar,@pageTotalCount)+' general_id from tresource where general_title like ''%'+@keyword+'%'' or general_description like ''%'+@keyword+'%'' and '+@searchField+' like ''%'+@treeNodeID+'%'' and public_tosee =2 order by general_id desc
           )xx
          ) and x.public_tosee=2
            
          
        ) y order by x.general_id asc'
     exec(@sql)
     set @sql='select count(*) from tresource where public_tosee =2 and general_title like ''%'+@keyword+
        '%'' or general_description like ''%'+@keyword+
        '%'' and '+@searchField+' like ''%'+ @treeNodeID+'%'''
     exec(@sql)
    end
   select  @returnValue = 1 ---数据检索执行成功
 end