SQL中的WHERE子句中的LIKE和NULL

I have a store procedure which i have planned to use for search and get all values.

我有一个存储过程，我计划用于搜索并获取所有的值。

Scenario: If the parameter passed is NULL it should return all the values of the table and if the parameter passed is not NULL it should return the values according to the condition which is in LIKE.

场景:如果传递的参数为NULL，它应该返回表的所有值，如果传递的参数不是NULL，它应该根据类似的条件返回值。

//Query:

/ /查询:

ALTER procedure [dbo].[usp_GetAllCustomerDetails]
(
@Keyword nvarchar(20) =  null
)
As
Begin

Select CustomerId,CustomerName,CustomerTypeName,CustomerCode,CategoryName,CustomerMobile,CustomerEmail,CustomerAddress,CustomerCity,CustomerState,Pincode
from tblCustomerMaster CM
inner join dbo.tblCustomerTypeMaster CTM on CTM.CustomerTypeId = CM.CustomerType
inner join dbo.tblCategoryMaster CCM on CCM.CategoryId= CM.CustomerCategory
where CustomerName like '%'+@Keyword+'%'

In the above query it returns no values when i execute since the NULL is assumed as string by SQL, so what should i write in the where clause to get the desired output?

在上面的查询中，当我执行时，它不会返回任何值，因为NULL是由SQL假定为string的，那么在where子句中我应该写什么来得到所需的输出呢?

3 个解决方案

#1

You can use condition like this in you where clause

你可以在where子句中使用这样的条件

where @Keyword is null or CustomerName like '%' + @Keyword + '%'

#2

I just want to point out another way of solving this problem. The issue is that the default value for @KeyWord is NULL. If you change the default to '', then the problem goes away:

我只是想指出另一种解决这个问题的方法。问题是@关键字的默认值为NULL。如果您将默认设置为“，那么问题就消失了:

ALTER procedure [dbo].[usp_GetAllCustomerDetails]
(
@Keyword nvarchar(20) = ''
)

Any non-NULL customer name would then be like '%%'.

任何非空的客户名都应该是'% '。

#3

You just need to add SET @Keyword = coalesce(@Keyword,'') to your procedure like this :

您只需将SET @Keyword = coalesce(@Keyword， ")添加到您的过程中，如下所示:

 ALTER procedure [dbo].[usp_GetAllCustomerDetails]
(
@Keyword nvarchar(20) =  null
)
As
Begin
SET @Keyword = coalesce(@Keyword,'')

Select CustomerId,CustomerName,CustomerTypeName,CustomerCode,CategoryName,CustomerMobile,CustomerEmail,CustomerAddress,CustomerCity,CustomerState,Pincode
from tblCustomerMaster CM
inner join dbo.tblCustomerTypeMaster CTM on CTM.CustomerTypeId = CM.CustomerType
inner join dbo.tblCategoryMaster CCM on CCM.CategoryId= CM.CustomerCategory
where CustomerName like '%'+@Keyword+'%'

#1