Sql Server 条件包含 Null 参数的自由文本搜索处理

Question

为了让这个查询继续进行，我已经努力了很长一段时间。

简而言之，我的查询通过 fileno 和/或搜索字段进行搜索

DECLARE @pSearchFor AS NVARCHAR(100);

- 我在这里使用 null 值、 ' ' 或单独的单词进行测试 SET @pSearchFor = null -- '“marsa”和“mosta”'；

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

declare @fileNo nvarchar(50) = 'e/e'

select top 1000 r.FileId, r.FileNo,  fs.SearchField, @pSearchFor

from regfile as r
left outer join FileSearchFields as fs on r.FileId = fs.FileID
where r.FileNo like 
CASE 
 WHEN  Len(@fileno) > 1 THEN '%'+@fileNo+'%'
 ELSE r.FileNo
END

 AND 
     1 = 
     CASE WHEN ISNULL(@pSearchFor, '') = '' THEN 1 ELSE 0 END
       or CONTAINS(fs.SearchField, @pSearchFor)

---

如果 @pSearchFor 为 null，我不会返回任何内容，否则效果很好。

如果 null ，我需要返回所有实例

一种可能的解决方案可能是调用 2 个单独的 sps 或使用 if /else 但可能存在更好的方法。

我真的很感谢你的帮助！

Answer 1

首先，将 @pSearchFor 设置为 ""：

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

这意味着这永远不会返回 1：

CASE WHEN ISNULL(@pSearchFor, '') = '' THEN 1 ELSE 0 END

您需要使用不同的变量，或者使用相同类型的 CASE select 列表中的 表达式，而不是将值从 NULL 更改为 ""。

SELECT TOP 1000 r.FileId, r.FileNo,  fs.SearchField, 
    CASE WHEN COALESCE(@pSearchFor, '') = '' THEN '""' ELSE @pSearchFor END

此外，您还使用 SELECT TOP 但没有 ORDER BY ...如果您想要一个子集，您不关心获得哪个子集吗？

Answer 2

我已经解决了这个问题。也许这对其他人有帮助！
这是我的存储过程的一个片段。

 @fileNo nvarchar(50) = null ,
 @fields nvarchar(100) = '""',`enter code here`
 @datefrom date = null,
 @dateto date  = null,
...
AS`enter code here`
BEGIN

 if (@fields = null or LEN(@fields) < 1 ) set @fields = '""'

select top 1000 r.*,
(CASE 
        WHEN fs.SearchField IS NULL THEN CONVERT(NVarChar(1),'')
        ELSE CONVERT(NVarChar(MAX),fs.SearchField)
     END) AS [Search]  
from regfile as r
left outer join FileSearchFields as fs on r.FileId = fs.FileID
where r.FileNo like 
CASE 
 WHEN  Len(@fileno) > 1 THEN '%'+@fileNo+'%'
 ELSE r.FileNo
 END
 and
  r.Date between 
CASE 
 WHEN  @datefrom != '' THEN @datefrom
 ELSE '1900-1-1'
 END

 and 
CASE 
 WHEN  @dateto != '' THEN @dateto
 ELSE '9999-1-1'
 END
 and 
 ((LEN(@fields) > 2 and contains(fs.SearchField,@fields))or (LEN(@fields) <= 2)) 
 --NB: <= 2 as we have added the "" characters in @fields!

end