Return all results (including null) when SQL stored procedure parameter is null

Question

I have SQL table that contains null values for ex:

ID	NAME	CODE
1	Joe	123
2	Doe	456
3	Soe	NULL

I want to check if the where clause parameter is null then return all table, what I tried:

@devCode int = NULL

SELECT * FROM myTable WHERE CODE = ISNULL(@devCode, CODE)

But the last row (ID 3) gets excluded when @devCode = NULL. I want that to be included too in result when the parameter is null.

p.s. I also tried IIF and CASE but the result is the same.

This is how null works... Doesn't match anything including null. — Salman Arshad
– Salman Arshad, Commented Mar 16, 2022 at 16:08
@SalmanA yes I learned that facing this, and I also learned that SET ANSI_NULLS OFF fix the problem nut it's not recommended to do so. — Sherif Riad
– Sherif Riad, Commented Mar 16, 2022 at 16:18

Arash Ghazi · Accepted Answer · 2022-03-16 16:07:26Z

3

I think it will work

SELECT * FROM myTable WHERE CODE = @devCode OR @devCode IS Null

answered Mar 16, 2022 at 16:07

Arash Ghazi

1,01112 silver badges18 bronze badges

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

2 Comments

Sherif Riad Over a year ago

Worked like a charm! I didn't think about it this way, I tried something similar but didn't work as expected: WHERE CODE = @devCode OR CODE IS Null Thank you!

Arash Ghazi Over a year ago

you are welcome happy coding

Collectives™ on Stack Overflow

Return all results (including null) when SQL stored procedure parameter is null

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related