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?
You can use condition like this in you where
clause
where @Keyword is null or CustomerName like '%' + @Keyword + '%'
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 '%%'.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With