Use IF condition in WHERE clause

Question

I have a date column in my table. I want to get all the records where the number of months compared to today's date is greater than 10, but I want to apply this condition only if my declared parameter has value @parameter='Apply'. Otherwise, I do not want to enforce the date criteria.

My results should be the 1st, 2nd, and 3rd rows when @parameter ='Apply', else if @parameter is something else, then all four rows would be returned.

  GetDate         Val
------------     -----
 1/12/2013         A
 1/12/2012         B
 1/12/2014         C
 1/12/2015         D

The following works as expected, but with lots of redundant code. Is there way to put the if condition in the where clause ?

DECLARE @parameter VARCHAR(10)='Apply'
if(@parameter='Apply')
begin
select * from testTable where
    DATEDIFF(MONTH,GetDate,GETDATE()) >10
end
else
begin
    select * from testTable
end

Relevant: Catch-all Queries

Felix Pamittan
– Felix Pamittan

2015-10-13 23:38:19 +00:00
Commented Oct 13, 2015 at 23:38 — Felix Pamittan
– Felix Pamittan, Commented Oct 13, 2015 at 23:38

DavidG · Accepted Answer · 2015-10-13 23:53:14Z

3

This will do the same thing:

select * 
from testTable 
where (DATEDIFF(MONTH,GetDate,GETDATE()) >10 AND @parameter='Apply')
OR @parameter <> 'Apply'

answered Oct 13, 2015 at 23:53

DavidG

120k13 gold badges232 silver badges239 bronze badges

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

1 Comment

ESG Over a year ago

This will have the same output, but is SQL server smart enough to optimize this in a way that makes sense?

Collectives™ on Stack Overflow

Use IF condition in WHERE clause

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related