MS-SQL 2005搜索：带有freetext的条件where子句

I'm writing a fairly complex stored procedure to search an image library.

我正在编写一个相当复杂的存储过程来搜索图像库。

I was going to use a view and write dynamic sql to query the view, but I need to use a full text index, and my view needs outer joins (MS-SQL 2005 full-text index on a view with outer joins)

我打算使用视图并编写动态sql来查询视图,但是我需要使用全文索引,并且我的视图需要外部联接(具有外部联接的视图上的MS-SQL 2005全文索引)

So, I'm back to a stored procedure.

所以,我回到了存储过程。

I need to search on (all optional):

我需要搜索(所有可选):

a general search query that uses the full text index (or no search terms)

使用全文索引(或没有搜索词)的常规搜索查询

one or more categories (or none)

一个或多个类别(或没有)

a single tag (or none)

单个标签(或没有)

Is there a way to do a conditional FREETEXT in the 'WHERE' clause? The query may be empty, in which case I want to ignore this, or just return all FTI matches.

有没有办法在'WHERE'子句中执行条件FREETEXT?查询可能为空,在这种情况下我想忽略它,或者只返回所有FTI匹配。

...AND FREETEXT(dbo.MediaLibraryCultures.*, '"* "') doesn't seem to work. Not sure how a case statement would work here.

...和FREETEXT(dbo.MediaLibraryCultures。*,'“*”')似乎不起作用。不确定案例陈述如何在这里起作用。

Am I better off inserting the category/tag filter results into a temp table/table variable, then joining the FTI search results? That way I can only do the join if the search term is supplied.

我最好将类别/标签过滤器结果插入临时表/表变量,然后加入FTI搜索结果?这样,如果提供搜索词,我只能进行连接。

Thoughts?

4 个解决方案

#1

I know it's a year later and a newer version of SQL but FYI...

我知道这是一年之后和更新版本的SQL但是我们...

I am using SQL Server 2008 and have tried to short circuit using

我正在使用SQL Server 2008并试图使用短路

AND ( @searchText = '' OR freetext(Name, @searchText))

and I receive the message "Null or empty full-text predicate" when setting @searchText = ''. I guess something in 2008 has changed that keeps short circuiting from working in this case.

当设置@searchText =''时,我收到消息“空或空全文谓词”。我想2008年的一些事情发生了变化,这使得在这种情况下的工作短路。

#2

You could add a check for the empty search string like

您可以添加对空搜索字符串的检查

where ...
AND (FREETEXT(dbo.MediaLibraryCultures.*, @FreeTextSearchFor) OR @FreeTextSearchFor = '')

(I have a feeling that freetext searches can't have null passed into them, so I'm comparing to an empty string)

(我觉得freetext搜索不能将null传递给它们,所以我要比较一个空字符串)

If the term to search for is empty, the whole clause will evaluate to true, so no restrictions will be applied (by this clause) to the rows returned, and of course since its a constant being compared to a variable - I would think the optimizer would come into play and not perform that comparison for each row.

如果要搜索的术语为空,则整个子句将评估为true,因此不会对返回的行应用(通过此子句)限制,当然因为它与变量进行比较 - 我会认为优化器将发挥作用,而不是为每一行执行该比较。

#3

Hmm, I thought there was no short-circuiting in sql server?

嗯,我以为sql server中没有短路?

AND (@q = '' OR FREETEXT(dbo.MediaLibraryCultures.*, @q))

seems to work just fine!

似乎工作得很好!

Strangely, the full text scan is still part of the execution plan.

奇怪的是,全文扫描仍然是执行计划的一部分。

#4

Doesn't work on SQL Server 2014. I tried the suggested short circuit in one of my stored procedures, but it keeps evaluating the FREETEXT expression. The only solution I have found is the following:

在SQL Server 2014上不起作用。我在我的一个存储过程中尝试了建议的短路,但它一直在评估FREETEXT表达式。我找到的唯一解决方案如下:

IF ISNULL(@Text, N'') = N'' SET @Text = N'""'

SELECT ...
   WHERE ...
   AND (@Text = '""' OR FREETEXT([Data], @Text)

#1