SQL Server CASE inside where clause not working for NULL value

Question

What I want is when @AddressCode is null then simply execute

SELECT
    *
FROM
    Addresses
WHERE
    1= 1

If @AddressCode is not NULL then execute

SELECT
    *
FROM
    Addresses
WHERE
    1= 1 AND @AddressCode=Addresses.AddressCode

How can I achieve this ... I tried query below and its not working for Address with NULL value or when @AddressCode is NULL .... (Addresses is just an imaginary table name)

Declare @AddressCode varchar(20)
Set @AddressCode=NULL

SELECT
    *
FROM
    Addresses
WHERE
    1= 1
    AND
    CASE @AddressCode
        WHEN NULL
        THEN Addresses.AddressCode
        ELSE @AddressCode
    END =Addresses.AddressCode

I tried hard but am not able to make it ... I know i can write IF statement and write individual select statement but don't want to do that as i have lots of such criteria as @AddressCode :(

ISNULL is your friend here...

Grantly
– Grantly

2014-12-11 21:53:09 +00:00
Commented Dec 11, 2014 at 21:53 — Grantly
– Grantly, Commented Dec 11, 2014 at 21:53

PaulStock · Accepted Answer · 2011-08-30 14:14:14Z

11

SELECT *
FROM
    Addresses
WHERE
    (@AddressCode IS NULL OR Addresses.AddressCode = @AddressCode)

answered Aug 30, 2011 at 14:14

PaulStock

11.3k9 gold badges51 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Martin Smith Over a year ago

If address code is indexed you would need to use RECOMPILE with this to prevent an unnecessary scan in the case that @AddressCode IS NOT NULL

prsthapit Over a year ago

Thanks it works Simply great :) Initially i missed that () in where so was messed up and i fixed it.

a1ex07 · Accepted Answer · 2011-08-30 14:28:31Z

4

You are using CASE improperly in this case (comparing with null). The right way is

CASE
  WHEN @AddressCode IS NULL THEN Addresses.AddressCode
  ELSE  @AddressCode
 END

The problem with your query is that CASE a WHEN b THEN c... tests if a=b which turns into FALSE(NULL) if one of the operands is null.
Another question is if you really need CASE here...

edited Aug 30, 2011 at 14:28

answered Aug 30, 2011 at 14:18

a1ex07

37.4k12 gold badges93 silver badges103 bronze badges

Collectives™ on Stack Overflow

SQL Server CASE inside where clause not working for NULL value

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related