How to use case in where statement

Beginner_2012 · January 31st, 2012, 09:25 PM

Can someone help me to get this query to work?

DECLARE @option bit
,@begindate datetime
,@enddate datetime
/@paymentdate datetime

Set @option = 1
,@begindate = ‘2/1/2012’
,@enddate = ‘2/20/2012’
,@paymentdate = '2/8/2012'

Select * from inventory i
where i.TransactionType = '810'
and (CASE @option
WHEN 1 THEN i.PaymentDueDate between @begindate and @enddate
ELSE i.CreateDate between @begindate and @enddate
END)

disel2010 · February 1st, 2012, 06:47 AM

Hi,

check out this previous thread to see why you cannot use a Case statement like that in the where clause:
Case Statement In Where Clause Logic

I would suggest to rewrite your sql as follows:

Code:

 
Select * from inventory i
where 
 i.TransactionType = '810'
 and 
 (
  ((@option = 1) and (i.PaymentDueDate between @begindate and enddate))
  or
  ((@option <> 1) and (i.CreateDate between @begindate and @enddate))
 )

Hope this helps

Beginner_2012 · February 2nd, 2012, 09:19 PM

Yes, it works. Thanks so much.