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
It is as simple as this:
WHERE customers = NULLIF(@customerID, '') OR NULLIF(@customerID, '') IS NULL
NULLIF(x, '') will convert
'' to null. Then we take advantage of how null comparison works:
@customerIDis not null then first condition will be true only for exact match
@customerIDis null then first condition will be false (nothing equals null) but second condition will be true for all rows