CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2016
James Wierzba
James Wierzba

Reputation: 17508

Use Ternary function in SQL Server to determine NULL | NOT NULL based on bit?

Say I have an input parameter to a stored procedure @flag

I want to filter my query based on some column col1 being null, based on the value of this flag.

Say if @flag = 1, show only records with col1 IS NULL, if @flag = 0, show only records with col1 IS NOT NULL

My intuition would lead me to this:

select * 
from table1 
where col1 IS IIF(@flag = 1, NULL, NOT NULL);

This does compile. Is there any other concise way to do this?

Upvotes: 2

Views: 149

Answers (1)

juergen d
juergen d

Reputation: 204756

Even when your query compiles I doubt it will work. But you can do it with boolean and/or logic

select * from table1 
where (@flag = 1 and col1 IS NULL)
   or (@flag = 0 and col1 IS NOT NULL)

Upvotes: 4

Related Questions