SET  QUOTED_IDENTIFIER  ON  

GO

SET  ANSI_NULLS  ON  

GO

-- 通过歌名进行全文索引-搜索歌曲

-- xiaopin

-- 2011-08-15

ALTER       procedure  p_show_infokey_bysongname

@key   varchar ( 128 )  = '' ,

@page   int   =   1 ,

@pagesize   int   =   10 ,

@total   int   =   - 1  output

as

set  nocount  on

set   transaction   isolation   level   read   uncommitted

set  xact_abort  on

set   @key = rtrim ( ltrim ( @key ))

declare   @upperbound   int

declare   @lowerbound   int

declare   @sql   nvarchar ( 2048 )

declare   @where   nvarchar ( 2048 )

declare   @searchid   int

declare   @searchkey   varchar ( 256 )

declare   @searchcount   int

set   @upperbound   =   @pagesize   *   @page

set   @lowerbound   =   @pagesize   *  (  @page   -   1  )

set   @searchid = isnull (( select  searchid  from  t_search  where  searchkey = '' + @key + '' ), 0 )

if   @searchid = 0

begin

     insert   into  t_search(searchkey) values ( @key )

     set   @searchid = @@identity

     set   @searchkey   =   ' "* '   +   @key   +   ' *" '

     insert   into  t_search_index(searchid,infoid,sizeid,userid,totaldown,normid)  select   top   1500   @searchid ,infoid,sizeid,userid,totaldown,normid  from  t_info  where   contains (title, @searchkey )  order   by  infoid  desc

end

if   @total   <=   0

begin

     set   @searchcount = isnull (( select   count ( 1 )  from  t_search_index  where  searchid  =   @searchid ), 0 )

     if (  @searchcount   =   0 )

     begin

         delete   from  t_search  where  searchid  =   @searchid

         delete   from  t_search_index  where  searchid  =   @searchid

     end

end

set   @where = '  where searchid= ' + convert ( varchar , @searchid )

exec  P_SplitPageOneSql_search  ' t_search_index ' , @pagesize , @page , ' b.totaldown desc,b.rid asc ' , ' b.totaldown asc,b.rid desc ' , ' b.totaldown,b.rid ' , ' rid ' , 1 , @where , ' a.infoid ' , @total  output

GO

SET  QUOTED_IDENTIFIER  OFF  

GO

SET  ANSI_NULLS  ON  

GO

SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ALTER                               procedure P_SplitPageOneSql_search   @tablename varchar(32),   @pagesize int,   --页面大小   @pagepos int,   --第几页   @orderby  varchar(64),    --排序顺序,多个则用","分隔 加上前缀b.   @orderby2 varchar(64),   --排序顺序的反向排序,多个则用","分隔 加上前缀b.   @orderbyclause varchar(64), --排序字段(不包括desc ,asc等),以","分隔  加上前缀b.   @keyname varchar(32),   --主键   @keyinorder tinyint ,       --主键是否在排序字段中 1:在  0:不在   @whereclause varchar(1024) , --where条件   @showclause varchar(1024),  --显示的字段 加上前缀a.  --注意如果显示字段过长的话,需要再加长   @totalcount int output  --是否需要统计总数 -1:要统计 其它不统计   as     set nocount on   set transaction isolation level read uncommitted   declare @sql nvarchar(1024) --注意如果显示字段过长的话,需要再加长   declare @showrows smallint  --显示的行数(可能在最后一页,这样显示的记录数就要小于@pagesize   declare @top9clause nvarchar(1024)   if @keyinorder = 0       set @top9clause = @orderbyclause + ',b.' + @keyname   else       set @top9clause = @orderbyclause    if @whereclause is null       set @whereclause = ''     if @totalcount <= 0   begin       set @sql = 'set @totalcount = (select count(1) from ' + @tablename + ' ' + @whereclause +')'       exec sp_executesql @sql,N'@totalcount int output',@totalcount output   end     --要处理好翻到最后一页且不能满页时的情况   if @totalcount > @pagesize * (@pagepos -1) and @totalcount < @pagesize * @pagepos       set @showrows = @totalcount - @pagesize * (@pagepos -1)   else if  @totalcount >= @pagesize * @pagepos       set @showrows = @pagesize   else      return      set @sql = 'select '   +   @showclause +',other.* from (   select top  ' + convert(varchar, @showrows)  + '  ' + @top9clause +  ' from(   select top ' + convert(varchar,@pagesize * @pagepos) + ' '     + @top9clause +  '  from     ' + @tablename  + '  b ' +  @whereclause  +' order by ' + @orderby + ') b    order by  '  + @orderby2 + ') b inner join ' + @tablename  + '  a on a.' + @keyname +' = b.' + @keyname +'     inner join t_info other on a.infoid = other.infoid   order by ' + @orderby     exec sp_executesql @sql  GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO