[MSSQL]使用預存程序對存在資料庫的文字欄位做多個關鍵字搜尋

2011-09-23

7009
0
MSSQL

使用預存程序對存在資料庫的文字欄位做多個關鍵字搜尋，關鍵字和關鍵字間字空格格開，像用google或奇摩搜尋一樣

KeywordList是輸入的一串關鍵字，CategoryID是我將文章做分類，所以搜尋時也可以選要搜的那個分類，DECLARE是宣告的意思，主要的目的是將關鍵字和SQL語法串成字串，再去使用exec去執行串好的語法做查詢。


/****** Object:  StoredProcedure [dbo].[SelectDocumentsBySearch]    Script Date: 09/23/2011 10:07:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SelectDocumentsBySearch]
    @KeywordList varchar(255),
    @CategoryID int
AS

DECLARE @next int  
DECLARE @Strlength int
DECLARE @strWhere nvarchar(2000)
DECLARE @strSelect nvarchar (255)
DECLARE @strWord nvarchar(255)

Set @next=1
set @strWhere=''
set @Strlength=dbo.Get_StrArrayLength(@KeywordList,',')

If (@CategoryID=0)
  SELECT @strSelect = 'Select * From vw_ifct_DocList Where '
else
  SELECT @strSelect = 'Select * From vw_ifct_DocList Where (CategoryID=' + convert(nvarchar, @CategoryID) + ') And '
  
while @next<=@Strlength
begin
  SELECT @strWord = ''
  SELECT @strWord = dbo.Get_StrArrayStrOfIndex(@KeywordList,',',@next)
 if (@next > 1)
  SELECT @strWhere = @strWhere + ' or '

  select @strWhere = @strWhere + ' Title like ''%' + @strWord+'%'' or Description like ''%' + @strWord + '%'' or keyword like ''%' + @strWord + '%'''
  SELECT @next=@next+1
end

exec(@strSelect+'(' + @strWhere + ')')

下例的Function是將空格的關鍵字做分隔

/****** Object:  UserDefinedFunction [dbo].[Get_StrArrayLength]    Script Date: 09/23/2011 10:21:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[Get_StrArrayLength]
(
 @str varchar(1024),  --要分割的字符串
 @split varchar(10)  --分隔符號
)
returns int
as
begin
 declare @location int
 declare @start int
 declare @length int
 set @str=ltrim(rtrim(@str))
 set @location=charindex(@split,@str)
 set @length=1
 while @location<>0
 begin
   set @start=@location+1
   set @location=charindex(@split,@str,@start)
   set @length=@length+1
 end
 return @length
end

回首頁