LIKE and NULL in WHERE clause in SQL

问题:

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.

//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?

回答1:

You can use condition like this in you where clause

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:

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 :

 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+'%'