15

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?

A Coder
  • 3,039
  • 7
  • 58
  • 129
  • Possible duplicate https://stackoverflow.com/questions/3924400/like-does-not-accept-null-value – O.Badr May 23 '16 at 12:26

3 Answers3

25

You can use condition like this in you where clause

where @Keyword is null or CustomerName like '%' + @Keyword + '%'
Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
  • Be carefully when use query like that because you will run in to performance issues. When you use @Keyword is null OR something, all indexes is not used. The query will slower. Read here: http://stackoverflow.com/questions/2161573/how-to-optimize-the-use-of-the-or-clause-when-used-with-parameters-sql-server – Hà Tây Quê Rượu Aug 30 '16 at 02:31
  • you may or may not run into performance issues with query. There're some techniques to avoid this, but I believe this question is more about generic where syntax – Roman Pekar Aug 30 '16 at 07:27
4

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

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

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+'%'
Ashish Gaur
  • 2,030
  • 2
  • 18
  • 32
  • don't recomment to do this. You'll end up with quere `where CustomerName like '%%'` and this is not the same as plain select all from table. – Roman Pekar Sep 08 '13 at 12:24
  • @RomanPekar This will only exclude `NULL` values, right ? or will there be any more difference ? – Ashish Gaur Sep 08 '13 at 12:27