SQL Server 2008 WHERE clause with CASE / WHEN and NULL value

Question

I'm trying to write something like :

SELECT *
FROM MyTable m
WHERE m.Category = CASE WHEN @Category IS NULL THEN m.Category ELSE @Category END

Basically, I need lines where m.Category equals @Category if this one has a value ; else, I need all lines.

BUT if m.Category has NULL value, the line is NOT send back because SQL needs something lile WHERE m.Category IS NULL and not WHERE m.Category = NULL.

So, how could I modify my request to tell to SQL : "I need ALL lines if @Category is null (even lines where m.Category is null), but if @Category has a value, I just need lines where m.Category matches @Category ?

roman · Accepted Answer · 2013-11-01 17:59:00Z

3

you can write it like this:

select *
from MyTable m
where @Category is null or m.Category = @Category

answered Nov 1, 2013 at 17:59

roman

118k30 gold badges205 silver badges209 bronze badges

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

3 Comments

AlexB Over a year ago

Ok, it was so easy I didn't even think about it... Thanks a lot

Steven Over a year ago

@roman-pekar - what do you call this? I used it about 5 years ago and had the need for it today and I couldn't for the life of me remember what it is called.

roman Over a year ago

@Steven well I don't know, not sure it has some specific name

Collectives™ on Stack Overflow

SQL Server 2008 WHERE clause with CASE / WHEN and NULL value

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related