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
Advertisement
Answer
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