如果变量为null，请勿在SQL WHERE子句中包含它。

Question

我在 SQL 中有一个查询，在用户在文本框中输入的所有值都为 null 的情况下，当他们点击搜索时，它将返回整个表。

需要注意的是，HTML 标签已保留。

SELECT Column Names
FROM TableName
WHERE
        FirstName LIKE '%' + @FirstName + '%'
        OR Surname LIKE '%' + @Surname + '%'
        OR City LIKE '%' + @City + '%'
        OR County LIKE '%' + @County + '%'

然而，我的目的是在搜索时不包括没有输入内容的FirstName。因此，如果有人决定在City中输入“London”，那么它将仅返回包含伦敦的结果。同样地，如果他们输入了“John”和“London”，那么我希望返回在伦敦所有名字为John的人。

- SetoChaos

Answer 1

(FirstName LIKE '%' + @FirstName + '%' OR @FirstName IS NULL )

- Randy

---

@Brad 你怎么看？如果FirstName为空，它将包括所有可能的名字。如果FirstName是''，那么它将拉入所有“FirstName Like '%%'”匹配的内容，这也会拉入所有内容。为了搞砸它们，他们必须输入一个空格。你需要LTRIM(RTRIM()) FirstName来弥补这个问题，但这是正确的做法。 - Eric J. Price

撤销！（哦等等...除非有编辑，否则此时无法更改投票，抱歉） - Brad

Answer 2

所以如果只有当@FirstName有值时才进行考虑，你可以通过以下方式实现：更改

FirstName LIKE '%' + @FirstName + '%'

转换为此

(@FirstName = '' OR FirstName LIKE '%' + @FirstName + '%')

当然，如果@FirstName作为null而不是空字符串传入，则会变成

(@FirstName IS NULL OR FirstName LIKE '%' + @FirstName + '%')

- Adam Rackis

Answer 3

其他答案是正确的，但我想通过为您的原始查询提供一些帮助来回报您：

 SELECT Column Names
FROM TableName
WHERE
  ((@FirstName is null and FirstName like '%') or (isnull(FirstName ,0) = @FirstName)) and
  ((@Surname is null and Surname like '%') or (isnull(Surname ,0) = @Surname)) and
  ((@City is null and City like '%') or (isnull(City ,0) = @City)) and
  ((@County is null and County like '%') or (isnull(County,0) = @County))

你只需要复制并粘贴这段内容

Jim

- James Perrett

---

+1 但原始问题在每个城市、名字、姓氏和国家之间使用了“OR”。 - Brad

好的，这基本上是可以工作的。如果我使用AND，那么显然需要所有文本框都有某种值。当所有值都输入时，它将仅返回正确的客户输入。但是，它不允许我例如只输入“约翰”，然后列出所有名字为“约翰”的客户。如果我更改为OR，那么如果我输入“约翰”，那么他的姓氏为“史密斯”，城市为南安普敦，州为汉普郡，那么它将列出所有名字叫约翰的人，他们的姓氏为“史密斯”等等。例如，我也得到了“戴夫·史密斯”，因为“史密斯”被输入为姓氏。 - SetoChaos

其中最令人困扰的可能是，如果我例如输入了“Hampshire”，然后列出了所有来自汉普郡的人。这意味着要找到我搜索的名字的人，我必须浏览汉普郡所有的人。 - SetoChaos

哦，我也注意到这个解决方案会去除输入任何以“J”开头的名称的功能。例如，如果您键入“Joh”，则应该仍然列出“John”。 - SetoChaos

实际上，是的，AND没问题。我已经将其更改为这样，现在它可以正常工作:D （由于某些原因，我无法发布“@”符号，因此我已将其删除）。((FirstName LIKE FirstName + '%') OR (ISNULL(FirstName,0) = FirstName)) AND ((Surname LIKE Surname + '%') OR (ISNULL(Surname,0) = Surname)) AND ((City LIKE City + '%') OR (ISNULL(City,0) = City)) AND ((County LIKE County + '%') OR (ISNULL(County,0) = @County)) - SetoChaos

Answer 4

这种方法可能会更加繁琐，但是根据数据集的大小和索引情况，您可以获得一些性能提升。

Declare @FirstName Nvarchar(10),
        @SurName Nvarchar(10),
        @City Nvarchar(10),
        @County Nvarchar(10),
        @SQL Nvarchar(Max),
        @OrCriteria Nvarchar(Max) = ''

Set     @SQL = 'Select  Column Names
                From    TableName '

If      @FirstName Is Not Null
Begin
        Set     @OrCriteria = @OrCriteria + 'FirstName Like ''%''' + @FirstName + '''%'' Or '
End         

If      @Surname Is Not Null
Begin
        Set     @OrCriteria = @OrCriteria + 'Surname Like ''%''' + @Surname + '''%'' Or '
End     

If      @City Is Not Null
Begin
        Set     @OrCriteria = @OrCriteria + 'City Like ''%''' + @City + '''%'' Or '
End     

If      @County Is Not Null
Begin
        Set     @OrCriteria = @OrCriteria + 'County Like ''%''' + @County + '''%'' Or '
End         

If      @OrCriteria <> ''
Begin
        Set     @SQL = @SQL + 'Where (' + Left(@OrCriteria,Len(@OrCriteria)-4) + ')'
End

Exec    sp_executeSQL @SQL

- Eric J. Price

---

谢谢，但我实在不太理解这个，因为它对我来说看起来很复杂，但是我得到了与Brad的解决方案相同的错误。 - SetoChaos

很奇怪，当你声明变量时出现错误。在SSMS中它是有效的，所以问题可能出在你使用的位置/方法上。如果你不理解它，我可以理解你不使用它。但它有益处的原因是，从技术上讲，它比其他解决方案具有性能优势，因为你只包括where条件中需要的列。使用这种解决方案，如果@FirstName为空，你就不会对该列运行逻辑检查，因为假定所有列都应该被包括在内。 - Eric J. Price

当创建具有多个表的大型复杂查询时，此方法非常有用。例如，如果您有一个条件变量需要连接到另一个大表，但实际上除了作为筛选方法之外，您并不需要该表。如果您有这样的场景，并且偶尔不需要该变量，则可以使用动态SQL构建查询，以避免完全连接到该表。我使用此方法成功地显着提高了某些查询的性能。干杯！ - Eric J. Price

Answer 5

declare @sql as varchar(300)
, @FirstName as varchar(50)
, @Surname as varchar(50)
, @City as varchar(50)
, @County as varchar(50)

set  @FirstName = 'myname'
set  @Surname = 'mylastname'
set  @City = null
set  @County = 'county'

set @sql= 'SELECT Column_Names
FROM TableName ' +    
 case when rtrim(ltrim(isnull(@FirstName,'') + isnull(@Surname,'') + isnull(@City,'') + isnull(@County,''))) = '' then '' else 'where ' end
 + coalesce('FirstName LIKE ''%' + nullif(@FirstName,'')+ '%'' or ', '') +
        + coalesce('Surname LIKE ''%' + nullif(@Surname,'')+ '%'' or ', '') +
        + coalesce('City LIKE ''%' + nullif(@City,'')+ '%'' or ', '') +
        + coalesce('County LIKE ''%' + nullif(@County,'')+ '%''', '')

    select @sql

@sql 的内容应该是：

SELECT Column_Names  FROM TableName where FirstName LIKE '%myname%' or Surname LIKE %mylastname%' or County LIKE '%county%'

然后，要使用您创建的动态字符串，您需要执行Exec @Sql

- Brad

---

谢谢，但是当我使用这个时，我得到了“在关键字'declare'附近有语法错误”的提示。：S - SetoChaos

Answer 6

在 SQL Server 中存在 ISNULL() 函数

那么

SELECT Column Names
FROM TableName
WHERE
        FirstName LIKE '%' + ISNULL((@FirstName, '') + '%'
        OR Surname LIKE '%' + ISNULL((@Surname, '') + '%'
        OR City LIKE '%' + ISNULL((@City, '') + '%'
        OR County LIKE '%' + ISNULL((@County, '') + '%'

- Jose Alonso