SQL - Include where condition if variable is not null

Question

I am trying to add a where condition to be checked only if variable @schoolId is not null otherwise return all results. Please know that If condition cannot be used because it is a very large query. This is just a part of it.

Table Name - Students

Id Name   SchoolId
1  John      6
2  Mark      6
3  Alice     12
4  Prince   null

Query

Select * from Students st
where st.SchoolId = Case when @schoolId  is not null then @schoolId else st.SchoolId End

So if @schoolId is null, I want to return all 4 rows. I want it to return results where SchoolId is null too if variable is null. Currently with above query it does not return 4th row

Yogesh Sharma · Accepted Answer · 2020-01-23 16:49:05Z

1

You can use Boolean logic instead of case expression :

where (@schoolId is null) or
      (st.SchoolId = @schoolId);

answered Jan 23, 2020 at 16:49

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

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

3 Comments

Learn AspNet Over a year ago

This won't work because it will only return null value if schoolId is not provided

Yogesh Sharma Over a year ago

. . . No, it will return all rows when @schoolId has no value.

Yogesh Sharma Over a year ago

@LearnAspNet. . . In your query with case expression, it will return 3 rows only when @schoolId is null. because null = null will evaluate as false.

Gordon Linoff · Accepted Answer · 2020-01-23 17:21:02Z

1

Simply use or:

where (st.SchoolId = @schoolId or @schoolId is null)

You have two separate conditions and this checks both of them.

edited Jan 23, 2020 at 17:21

answered Jan 23, 2020 at 16:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

Learn AspNet Over a year ago

This won't work because it won't return the 3rd row if SchoolId is not provided

Gordon Linoff Over a year ago

@LearnAspNet . . . This does exactly what you are asking for. Why would you say the 3rd row would not be returned? This returns ALL rows when @schoolid is null.

Learn AspNet Over a year ago

Sorry, my bad. if you edit your answer in any way. I can upvote you. It does not let me change the vote

FELIX A · Accepted Answer · 2020-01-23 18:10:45Z

0

Check this out:

declare @schoolId int

If @schoolId is null
Select * 
from dbo.Students st
Else
Select * 
from dbo.Students st
where st.SchoolId = @schoolId

answered Jan 23, 2020 at 18:10

FELIX A

155 bronze badges

Collectives™ on Stack Overflow

SQL - Include where condition if variable is not null

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related