SQL 中 WHERE 子句中的 LIKE 和 NULL [英] 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.

场景:如果传入的参数为NULL，则返回表中的所有值，如果传入的参数不是NULL，则根据LIKE中的条件返回值.

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.

//查询:

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

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

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?

推荐答案

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

You can use condition like this in you where clause

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

这篇关于SQL 中 WHERE 子句中的 LIKE 和 NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！