LIKE and NULL in WHERE clause in SQL

sql sql-server sql-server-2008 where-clause sql-like
A Coder picture A Coder · Sep 8, 2013 · Viewed 31.8k times · Source

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?

Answer

Roman Pekar picture Roman Pekar · Sep 8, 2013

You can use condition like this in you where clause

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

Related questions

Conditional WHERE clause in SQL Server

I am creating a SQL query in which I need a conditional where clause. It should be something like this: SELECT DateAppr, TimeAppr, TAT, LaserLTR, Permit, LtrPrinter, JobName, JobNumber, JobDesc, ActQty, (ActQty-LtrPrinted) AS L, (ActQty-QtyInserted) AS M, ((ActQty-LtrPrinted)-(ActQty-QtyInserted)) …

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More