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屋!