基于sql-server 的全文检索

/*
[搜索引擎－使用表变量]
版    本：2.0
最后修改：2007-1-10
*/

CREATE  PROCEDURE [dbo].[Search_Engine]   
   @Pagesize int,           --接收每页显示的多少数据
   @Pageindex int,              --接收当前显示的页码
   @Docount bit,                --判断是否是第一次接收数据，第一次接收时统计数据总数，否则不统计。
   @search_title1 varchar(20),  --接收第一个搜索关键字
   @search_title2 varchar(20),  --接收第二个搜索关键字
   @search_title3 varchar(20),  --接受第三个搜索关键字
   @search_number int           --设定传入几个值
AS

DECLARE

   @PageLowerBound int,
   @PageUpperBound int

DECLARE

   @indextable table(id int identity(1,1),nid int)

BEGIN

/*当传为值为1时运行下列(开始)*/

   IF @search_number=1

   BEGIN

   CREATE TABLE #temp_search1 (ID int PRIMARY KEY IDENTITY (1,1) NOT NULL,Search_Id int);

   INSERT INTO #temp_search1(Search_Id)
   SELECT id from [business_info2] where contains(medicine_name,@search_title1) order by id desc 

   INSERT INTO #temp_search1(Search_Id)
   SELECT id from [business_info2] where contains(medicine_type,@search_title1) order by id desc

   INSERT INTO #temp_search1(Search_Id)
   SELECT id from [business_info2] where contains(*, @search_title1)  order by id desc

   CREATE TABLE #search_table1 (ID int PRIMARY KEY IDENTITY (1,1) NOT NULL,Search_Id int,Medicine_Name nvarchar(200),Medicine_Type nvarchar(200),Company_Name nvarchar(200),Product_capability ntext);

   INSERT INTO #search_table1(Search_Id,Medicine_Name,Medicine_Type,Company_Name,Product_capability)
   SELECT id,Medicine_Name,Medicine_Type,Company_Name,Product_capability from [business_info2] where Id in (select top 500 Search_Id from #temp_search1 order by id asc)

 /*分页存储过程开始*/

 IF(@Docount=1)

     Select Count(Id) From #temp_search1

 ELSE

 BEGIN

    Set @PageLowerBound=(@pageindex-1)*@pagesize
    Set @PageUpperBound=@PageLowerBound+@pagesize

    Set rowcount @PageUpperBound

 INSERT INTO @indextable(nid) select Id from #search_table1 order by Id asc 
    Select TOP 500 O.* From #search_table1 O,@indextable T where O.Id=T.nid and T.id>@PageLowerBound and T.id<=@PageUpperBound order by T.id

 END
 
 /*分页存储过程结束*/
 
 END

/*当传为值为3时运行下列(结束)*/

/*当传为值为2时运行下列(开始)*/

   IF @search_number=2

   BEGIN

   DECLARE @all_word nvarchar(50)

   SET @all_word='"'+@search_title1+'" near "'+@search_title2+'"'

   CREATE TABLE #temp_search2 (ID int PRIMARY KEY IDENTITY (1,1) NOT NULL,Search_Id int);

   INSERT INTO #temp_search2(Search_Id)
   SELECT id from [business_info2] where contains(medicine_name,@all_word) order by id desc 

   INSERT INTO #temp_search2(Search_Id)
   SELECT id from [business_info2] where contains(medicine_type,@all_word) order by id desc
 
   INSERT INTO #temp_search2(Search_Id)
   SELECT id from [business_info2] where contains(medicine_name,@search_title1) order by id desc 

   INSERT INTO #temp_search2(Search_Id)
   SELECT id from [business_info2] where contains(medicine_name,@search_title2) order by id desc 

   INSERT INTO #temp_search2(Search_Id)
   SELECT id from [business_info2] where contains(medicine_type,@search_title1) order by id desc 

   INSERT INTO #temp_search2(Search_Id)
   SELECT id from [business_info2] where contains(medicine_type,@search_title2) order by id desc

   INSERT INTO #temp_search2(Search_Id)
   SELECT id from [business_info2] where contains(*,@all_word) order by id desc

   CREATE TABLE #search_table2 (ID int PRIMARY KEY IDENTITY (1,1) NOT NULL,Search_Id int,Medicine_Name nvarchar(200),Medicine_Type nvarchar(200),Company_Name nvarchar(200),Product_capability ntext);

   INSERT INTO #search_table2(Search_Id,Medicine_Name,Medicine_Type,Company_Name,Product_capability)
   SELECT id,Medicine_Name,Medicine_Type,Company_Name,Product_capability from [business_info2] where Id in (select top 500 Search_Id from #temp_search2 order by id asc)
 
 /*分页存储过程开始*/

 IF(@Docount=1)

     Select Count(Id) From #temp_search2

 ELSE

 BEGIN

    Set @PageLowerBound=(@pageindex-1)*@pagesize
    Set @PageUpperBound=@PageLowerBound+@pagesize

    Set rowcount @PageUpperBound

 INSERT INTO @indextable(nid) select Id from #search_table2 order by Id asc 
    Select TOP 500 O.* From #search_table2 O,@indextable T where O.Id=T.nid and T.id>@PageLowerBound and T.id<=@PageUpperBound order by T.id

 END

 /*分页存储过程结束*/

   END

/*当传为值为2时运行下列(结束)*/

/*当传为值为3时运行下列(开始)*/

   IF @search_number=3

   BEGIN
  
   DECLARE @all_word1 nvarchar(50)
   DECLARE @all_word2 nvarchar(50)
   DECLARE @all_word3 nvarchar(50)
   DECLARE @all_word4 nvarchar(50)

   SET @all_word1='"'+@search_title1+'" near "'+@search_title2+'" near "'+@search_title3+'"'
   SET @all_word2='"'+@search_title1+'" near "'+@search_title2+'"'
   SET @all_word3='"'+@search_title2+'" near "'+@search_title3+'"'
   SET @all_word4='"'+@search_title1+'" near "'+@search_title3+'"'
  
   CREATE TABLE #temp_search3 (ID int PRIMARY KEY IDENTITY (1,1) NOT NULL,Search_Id int);

   INSERT INTO #temp_search3(Search_Id)
   SELECT id from [business_info2] where contains(medicine_name,@all_word1) order by id desc

   INSERT INTO #temp_search3(Search_Id)
   SELECT id from [business_info2] where contains(medicine_type,@all_word1) order by id desc

   INSERT INTO #temp_search3(Search_Id)
   SELECT id from [business_info2] where contains(medicine_name,@all_word2) order by id desc

   INSERT INTO #temp_search3(Search_Id)
   SELECT id from [business_info2] where contains(medicine_name,@all_word3) order by id desc
 
   INSERT INTO #temp_search3(Search_Id)
   SELECT id from [business_info2] where contains(medicine_name,@all_word4) order by id desc

   INSERT INTO #temp_search3(Search_Id)
   SELECT id from [business_info2] where contains(medicine_type,@all_word2) order by id desc

   INSERT INTO #temp_search3(Search_Id)
   SELECT id from [business_info2] where contains(medicine_type,@all_word3) order by id desc
 
   INSERT INTO #temp_search3(Search_Id)
   SELECT id from [business_info2] where contains(medicine_type,@all_word4) order by id desc 

   INSERT INTO #temp_search3(Search_Id)
   SELECT id from [business_info2] where contains(medicine_name,@search_title1) order by id desc 

   INSERT INTO #temp_search3(Search_Id)
   SELECT id from [business_info2] where contains(medicine_name,@search_title2) order by id desc 

   INSERT INTO #temp_search3(Search_Id)
   SELECT id from [business_info2] where contains(medicine_name,@search_title3) order by id desc 

   INSERT INTO #temp_search3(Search_Id)
   SELECT id from [business_info2] where contains(medicine_type,@search_title1) order by id desc
 
   INSERT INTO #temp_search3(Search_Id)
   SELECT id from [business_info2] where contains(medicine_type,@search_title2) order by id desc
 
   INSERT INTO #temp_search3(Search_Id)
   SELECT id from [business_info2] where contains(medicine_type,@search_title3) order by id desc

   INSERT INTO #temp_search3(Search_Id)
   SELECT id from [business_info2] where contains(*,@all_word1) order by id desc

   CREATE TABLE #search_table3 (ID int PRIMARY KEY IDENTITY (1,1) NOT NULL,Search_Id int,Medicine_Name nvarchar(200),Medicine_Type nvarchar(200),Company_Name nvarchar(200),Product_capability ntext);

   INSERT INTO #search_table3(Search_Id,Medicine_Name,Medicine_Type,Company_Name,Product_capability)
   SELECT id,Medicine_Name,Medicine_Type,Company_Name,Product_capability from [business_info2] where Id in (select top 500 Search_Id from #temp_search3 order by id asc)

 /*分页存储过程开始*/

 IF(@Docount=1)

     Select Count(Id) From #temp_search3

 ELSE

 BEGIN

    Set @PageLowerBound=(@pageindex-1)*@pagesize
    Set @PageUpperBound=@PageLowerBound+@pagesize

    Set rowcount @PageUpperBound

 INSERT INTO @indextable(nid) select Id from #search_table3 order by Id asc 
    Select TOP 500 O.* From #search_table3 O,@indextable T where O.Id=T.nid and T.id>@PageLowerBound and T.id<=@PageUpperBound order by T.id

 END

 /*分页存储过程结束*/

   END

/*当传为值为3时运行下列(结束)*/

END

GO