CODEWITHSUNDEEP
sql-servert-sqlcaseisnullcase-when
The Light
The Light

Reputation: 27011

How to ignore a TSQL Where condition when the value of a filter is null in one statement?

How to ignore a where condition when the value of a filter is null in one statement?

-- if @filter1LowerBound is null then
select * from MyTable

-- if @filter1LowerBound is not null then
select * from MyTable
where column1 > @filter1LowerBound

Would that be possible to write one single where condition to handle the above situations?

I know I can CASE WHEN statement when my column is using "=".

or like:

select * from MyTable
where column1 = ISNULL(@filter1LowerBound, column1)

But I am having greater than and less than operators.

I have more greater than and less than filters like that so I'd need to make it work.

How would that be achieved?

Thanks

Upvotes: 1

Views: 4103

Answers (3)

Bob Vale
Bob Vale

Reputation: 18474

How about

select * from MyTable where 
   (@filter1LowerBound is null or column1 > @filter1LowerBound)

Upvotes: 3

Bort
Bort

Reputation: 7618

One way is to combine the clauses like so:

SELECT * FROM MyTable 
WHERE (@filter1LowBound IS NULL OR column1 > @filter1LowerBound)
   -- AND other conditions here

Upvotes: 5

podiluska
podiluska

Reputation: 51494

Assuming column1 is numeric

select * from MyTable where column1 > ISNULL(@filter1LowerBound, column1-1)
select * from MyTable where column1 < ISNULL(@filter1LowerBound, column1+1)

Similarly, if it's a string

select * from MyTable where column1 > ISNULL(@filter1LowerBound, '')

etc

Upvotes: 2

Related Questions