包含方法的可选搜索参数

The following SQL should demonstrate what I am trying to do. Unfortunately, when I try to run it, I get an error saying "Null or empty full-text predicate".

以下SQL应该演示我想要做的事情。不幸的是,当我尝试运行它时,我收到一条错误,说“空或全空谓词”。

DECLARE 
    @essaySearchTerm varchar(100), 
    @game_id varchar(100)
SET @essaySearchTerm = NULL; 
SET @game_id = 2; 

SELECT * FROM tblImageInfo i
WHERE i.game_id = @game_id 
        AND (@essaySearchTerm IS NULL OR CONTAINS(i.essay, @essaySearchTerm))

What does this error mean? How can I fix this?

这个错误是什么意思?我怎样才能解决这个问题?

2 个解决方案

#1

This is because SQL Server operators are not guaranteed to be short-circuited, so if you have an expression 'A OR B', sometimes both A and B will be evaluated regardless of logical value of A. See this post or this one for more details.

这是因为SQL Server运算符不能保证短路,所以如果你有一个表达式'A OR B',有时A和B都会被评估,无论A的逻辑值如何。请参阅这篇文章或者这篇文章以获取更多信息细节。

In your case, when @essaySearchTerm is null, it tries to evaluate CONTAINS(i.essay, NULL), which is illegal. After changing your code to:

在你的情况下,当@essaySearchTerm为null时,它会尝试评估CONTAINS(i.essay,NULL),这是非法的。将代码更改为:

IF ISNULL(@essaySearchTerm, '') = ''
SET @essaySearchTerm = '""';

SELECT * FROM tblImageInfo i
WHERE i.game_id = @game_id 
  AND (@essaySearchTerm = '""' OR CONTAINS(i.essay, @essaySearchTerm))

the right side of the OR will be a legal expression, as CONTAINS(x, '""') returns an empty set.

OR的右侧将是一个合法的表达式,因为CONTAINS(x,'“”)返回一个空集。

#2

Set the value of @essaySearchTerm to "" (two double quotes) if its null or empty:

如果@essaySearchTerm的值为null或为空,则将其设置为“”(两个双引号):

if @essaySearchTerm is null
set @essaySearchTerm='""';

#1