I'm trying to do a wildcard retrieve if a defined variable is blank. But I can't figure out the correct syntax.

SELECT...
FROM...
WHERE customers = CASE
WHEN ISNULL(@customerID, '') = '' THEN
LIKE '%' ELSE
@customerID END

CodePudding user response：

It is as simple as this:

WHERE customers = NULLIF(@customerID, '') OR NULLIF(@customerID, '') IS NULL

The expression NULLIF(x, '') will convert '' to null. Then we take advantage of how null comparison works:

• If @customerID is not null then first condition will be true only for exact match
• If @customerID is null then first condition will be false (nothing equals null) but second condition will be true for all rows

DB<>Fiddle

CodePudding user response：

You can just simply put it like this

SELECT...
FROM...
WHERE customers = ISNULL(@customerID,'') OR (ISNULL(@customerID,'') = '')

This should work because if the first condition was met, it will select the specific customer id. Otherwise, if the customer id is null,it will select all data because of this condition below:

(ISNULL(@customerID,'') = ''

If NULL, '' = '' will be true.