Don'；如果变量为null，则不要在SQL WHERE子句中包含该变量

本文关键字：变量 SQL WHERE 包含该 子句 如果 Don null | 更新日期: 2023-09-27 17:51:11

我在SQL中有一个查询，目前如果用户在文本框中输入的所有值都为null，那么当他们单击搜索时，它只会返回整个表。

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"answers"London"，那么我希望所有在伦敦打电话给约翰的人都能回来。

Don'；如果变量为null，则不要在SQL WHERE子句中包含该变量

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

其他答案都是正确的，但我想我会把一些东西放回你原来的查询中：

 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

所以您只想在@FirstName有值的情况下考虑它？你可以通过改变来做到这一点

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

到这个

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

当然，如果@FirstName是null而不是空字符串，那么它将是

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

它更麻烦，但根据数据集和索引的大小，通过这种方法可以获得一些性能提升。

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

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

在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, '') + '%'
相关文章：