/*
[搜索引擎-使用表变量]
版 本: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