-2

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
Salman A
  • 262,204
  • 82
  • 430
  • 521
user3063856
  • 3
  • 1

2 Answers2

0

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

Salman A
  • 262,204
  • 82
  • 430
  • 521
  • Sorry, I think I wasn't clear. The variable to be blank is the @customerID. It should then retrieve all customers in that case. WHEN CustomerID = '' THEN *fetch all customers ELSE *fetch customers with customerID* END Your solution attempts to retrieve any entries that don't have a customerID. – user3063856 Dec 10 '21 at 12:48
  • It does exactly that (read the two bullet points). But this query assumes that blank means null. If you want to consider `''` as blank then one tweak is needed. What rdbms are you using? – Salman A Dec 10 '21 at 12:54
  • I'm unsure of the rdbms as the SQL is run through a third party software. I've tried out your solution and it retrieves multiple customer IDs both when customerID is populated and also when blank. I appreciate the link to fiddle, but now I'm more confused why I'm experiencing different behaviour. – user3063856 Dec 10 '21 at 13:28
  • Unfortunately I cannot answer any further than this. This answer is based on how standard (ANSI) SQL works. – Salman A Dec 10 '21 at 13:31
  • Hi again, This ended up being exactly what I needed, and the error was due to some malformed data in our test environment. Thanks a lot for taking the time to respond and explain it all to me. You've been a great help! – user3063856 Dec 10 '21 at 13:49
0

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.

fjb
  • 1
  • 2
  • Thanks for the response. Unfortunately this solution hasn't worked for me as I receive an error when inputting a blank value for customerID. – user3063856 Dec 10 '21 at 13:30