Take another look at your
WHERE
clause:
(@CUST_NAME IS NULL OR [LOCATION] LIKE '%' + @CUST_NAME + '%')
OR
(@CUST_CODE IS NULL OR [CUST_CODE] LIKE '%' + @CUST_CODE + '%')
You've used
OR
to combine the filters for both parameters. Therefore, if
either parameter is
NULL
, then all records will be returned.
I suspect you mean to use
And
instead:
(@CUST_NAME IS NULL OR [LOCATION] LIKE '%' + @CUST_NAME + '%')
AND
(@CUST_CODE IS NULL OR [CUST_CODE] LIKE '%' + @CUST_CODE + '%')
You also need to appreciate the difference between
NULL
and an empty string. In your example, you're passing in an empty string, so
@CUST_CODE IS NULL
will be false. As a result, any rows where
CUST_CODE
is
NULL
will be excluded from your results, since they won't pass the
LIKE '%%'
test.
Therefore, I suspect you need:
(@CUST_NAME IS NULL OR @CUST_NAME = '' OR [LOCATION] LIKE '%' + @CUST_NAME + '%')
AND
(@CUST_CODE IS NULL OR @CUST_CODE = '' OR [CUST_CODE] LIKE '%' + @CUST_CODE + '%')