Case Statement in Where Clause NULL case

Question

I'm creating a query (to eventually be used in a stored procedure) with multiple variations on what criteria is entered in a form. Sometimes there can be an entry, sometimes not. Sometimes the data field has a value, sometimes it's NULL.

The fields in my form are NAME, SSN, and DRLICENSE.

DECLARE @name VARCHAR(30);    
DECLARE @ssn VARCHAR(10);
DECLARE @drlic VARCHAR(10);
--(if for example, someone enters data in two of the fields like this...)
SET @name = 'SMITH'
SET @drlic = 'D'

(In stored procedure)
SET @name = @name + '%'
SET @ssn = @ssn + '%'
SET @drlic = @drlic + '%'

SELECT 
    NAME,
    SSN,
    DRLICENSE
FROM 
    TABLE
WHERE 
    NAME LIKE CASE WHEN LEN(@name) > 1 THEN @name ELSE NAME END
    AND SSN LIKE CASE WHEN len(@ssn) > 1 THEN @ssn ELSE SSN END
    AND DRLICENSE LIKE CASE WHEN LEN(@drlic) > 1 THEN @drlic ELSE DRLICENSE END

The idea behind my case statement is to check the variable for usage and perform a like if the name, ssn, or drlicense are partial entries.

My question is: how do I account for the case of NULL in the table column (i.e. SSN LIKE SSN does not work when SSN is NULL because SSN IS NULL needs to be there).

Please do yourself a favor and don't store things like SSN in clear text. That data should be encrypted at all times and readable by VERY few people in your organization. — Sean Lange, Sep 25 '18 at 20:03

S3S · Accepted Answer · 2018-09-25T19:38:12.863

2

This isn't going to perform well, especially since you are using LIKE but parentheses are important here

where ((@name is null or name like '%' + @name + '%') or ( name is null))
and ((@ssn is null or ssn like '%' + @ssn + '%') or (ssn is null))
and ((@drlic is null or DRLICENSE like '%' + @drlic + '%') or (DRLICENSE is null))

This will return the NULL values in those columns regardless if a parameter is passed in or not. If you don't want the NULL then just remove the or ( name is null)) section from each line. I wasn't certain from your post.

edited Sep 25 '18 at 19:38

answered Sep 25 '18 at 19:35

S3S

24,809
5
26
45

Your suggestion is to take the CASE statement out? – alcor8 Sep 25 '18 at 19:37
yes, @alcor8 it isn't necessary though you *could* use a `CASE` – S3S Sep 25 '18 at 19:38

score 1 · Answer 2 · answered Sep 25 '18 at 19:35

1

if length(@foo) <= 1
  set @foo = NULL
if length(@bar) <= 1
  set @bar = NULL
...
WHERE (@foo is NULL or t.foo = @foo)
  AND (@bar is NULL or t.bar like @bar)
OPTION (RECOMPILE)

answered Sep 25 '18 at 19:35

Ivan Starostin

8,798
5
21
39

4

Please provide some explanation of how this solution applies to the question, bare code without comments and explanations is not, IMHO, that helpful. – Pawel Veselov Sep 25 '18 at 20:30

Igor · Answer 3 · 2018-09-25T19:49:59.770

You can inline adding the wild card to the parameter value and add your null checks inline as well. There is no need for a CASE statement either. For each of the parameter/column values the result returns true if the parameter is null or if the column value is null or if the column value starts with the passed in parameter value.

Where statement:

WHERE (Name IS NULL OR @name IS NULL OR Name LIKE @name + '%')
  AND (SSN IS NULL OR @ssn IS NULL OR SSN LIKE @ssn + '%')
  AND (DRLICENSE IS NULL OR @drlic IS NULL OR DRLICENSE LIKE @drlic + '%')

The key here is to not do any filtering if the incoming value to use is null or if the column value is null which I assume you do not want filtered on if it is null.

score 1 · Answer 4 · answered Sep 25 '18 at 19:42

This should do it:

WHERE (@name  IS NULL OR name      LIKE @name  + '%')
AND   (@ssn   IS NULL OR ssn       LIKE @ssn   + '%')
AND   (@drlic IS NULL OR drlicense LIKE @drlic + '%')

Note that if, for example, user searched for name = foo then it will return rows that match foo% and not rows that have NULL name.

Gordon Linoff · Answer 5 · 2018-09-25T20:15:53.513

If I understand correctly, you want a NULL-safe equality. That is, you want NULL to match NULL and values to match values. So:

where (name = @name or (name is null and @name is null)) and
      (ssn = @ssn or (ssn is null and @ssn is null)) and
      (drlicense = @drlicense or (drlicense is null and @drlicense is null))

or, an intriguing variant:

where (select count(*)
       from (select distinct *
             from (values (name, 'name'), (@name, 'name'), (ssn, 'ssn'), (@ssn, 'ssn'), (drlicense, 'drlicense'), (@drlicense, 'drlicense')
                  v(val, which)
            ) x
      ) = 3

This is using group by semantics to combine NULL values.

Or, if you want some obfuscation:

where nullif(name, @name) is null and nullif(@name, name) is null and
      nullif(ssn, @ssn) is null and nullif(@ssn, ssn) is null and
      nullif(drlicense, @drlicense) is null and nullif(@drlicense, drlicense) is null

score 0 · Answer 6 · answered Sep 25 '18 at 20:07

You could use ISNULL() it's very useful in cases like yours.

SELECT 
    NAME,
    SSN,
    DRLICENSE
FROM 
    TABLE
WHERE 
    ISNULL(NAME,0)      LIKE ISNULL(ISNULL(@name, NAME),0)
AND ISNULL(SSN,0)       LIKE ISNULL(ISNULL(@ssn, SSN),0)
AND ISNULL(DRLICENSE,0) LIKE ISNULL(ISNULL(@drlic, DRLICENSE),0)

This method will replace the NULL values with the column value if the column value is NULL then both will be set to 0 (which will be 0=0, which will always be true).

Case Statement in Where Clause NULL case

6 Answers6