CODEWITHSUNDEEP
sql
Learn AspNet
Learn AspNet

Reputation: 1622

SQL - Include where condition if variable is not null

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

Upvotes: 0

Views: 3132

Answers (3)

FELIX A
FELIX A

Reputation: 15

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270421

Simply use or:

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

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

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use Boolean logic instead of case expression : 

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

Upvotes: 1

Related Questions