7645空或全空全文谓词 [英] 7645 Null or empty full-text predicate

问题描述

我有一个在SQL2005上运行正常的查询，但将数据库移动到SQL2008给了我错误的标题。

问题的代码是一个调用以CONTAINS，CONTAINSTABLE或FREETEXT为空参数。不过，我试图只在有这样的值时才调用或加入。

  where（@search_term =''or（ FREETEXT（lst.search_text，@search_term）））
  

或

  left join containsstable（listing_search_text，search_text，@search_term）ftb on l.listing_id = ftb。[key] 
 and len（@search_term）> 0 
  

然而，我无法找到任何解决方法来处理SQL2008。任何想法？

我知道我可以做动态SQL，或者有两种不同情况的if语句（选择FT join，选择没有FT join的任何更好的解决方法，是否需要这样做？

解决方案

在将我自己的数据库从SQL 2005转换为SQL 2008时，我今天找到了答案。

传递作为您的搜索字词并更改@search_term = ''测试为 @search_term =''''
SQL服务器将忽略双引号并且不会引发错误。

例如，以下实际上会返回Users表中的所有记录：

  declare @SearchTerm nvarchar（250）
 
 SET @SearchTerm =''
 
从dbo.Users中选择UserId，U.Description，U.UserName 
 
 WHERE（（@SearchTerm ='''）或CONTAINS（（U.Description，U.UserName），@SearchTerm））
  

如果您正在使用。 Net，你可能会拿到E. W. Bachtal的FullTextSearch类的副本。他的网站非常翔实： http://ewbi.blogs.com/develops/

I have a query that ran fine on SQL2005 but moving the database to SQL2008 gives me the error from the title.

The code that is the problem is a call to CONTAINS, CONTAINSTABLE or FREETEXT with an empty parameter. However I'm trying to only call or join when there is a value like such

where (@search_term = '' or (FREETEXT(lst.search_text, @search_term)))

or

left join containstable (listing_search_text, search_text,  @search_term) ftb on l.listing_id = ftb.[key] 
    and len(@search_term) > 0

However I cannot find any workaround for this to work on SQL2008. Any ideas?

I know I can do dynamic SQL or have a if statement with two different cases (select with FT join, select without FT join. Any better workaround which doesn't require doing this?

解决方案

I found the answer to this today when converting my own database from SQL 2005 to SQL 2008.

Pass "" for your search term and change the @search_term = '' test to be @search_term = '""' SQL server will ignore the double quotes and not throw an error.

For example, the following would actually returns all records in the Users table:

declare  @SearchTerm nvarchar(250)

SET @SearchTerm = '""'

select UserId, U.Description, U.UserName
from dbo.Users U
WHERE ((@SearchTerm = '""') OR CONTAINS( (U.Description, U.UserName), @SearchTerm))

If you are using .Net, you might grab a copy of E. W. Bachtal's FullTextSearch class. His site is very informative: http://ewbi.blogs.com/develops/

这篇关于7645空或全空全文谓词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！