Case Statement in Where clause with parameters SQL Server

Question

Hi everyone,

In my function I am trying to use something like this

-----------------------

Select

COl1,Col2...

WHere Case @param1 When 1 Then myDate1 Between @DateParam1 And @DateParam2

When 2 Then myDate2 Between @DateParam1 And @DateParam2

Else myDate3 Between @DateParam1 And @DateParam2

END

And "some more conditions here"

------------------------------

I am getting error message near Between.

Please help me in solving this.

Thanks in advance.

Answer 1

Try

Select

COl1,Col2...

WHere 1 = Case  When @param1 and myDate1 Between @DateParam1 And @DateParam2 then 1

When @param1=2 and myDate2 Between @DateParam1 And @DateParam2 then 1

when @param1 not IN (1,2) and  myDate3 Between @DateParam1 And @DateParam2 then 1 else 0 -- none of the cases

END

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

You need to put the between after the case, not in it.

Declare @tvTable Table (
	Col1 varchar(10)
	,Col2 varchar(10)
	,myDate1 datetime
	,myDate2 datetime
	,myDate3 datetime
)
Insert	@tvTable
Values	('data', 'other', '20130101', '20130126', '20130401')
		,('something', 'blah', '20130201', '20130205', '20130101')
		,('foo', 'bar', '20130301', '20130601', '20130201')

Declare @param1 int
		,@DateParam1 datetime
		,@DateParam2 datetime

Select	@param1 = 2
		,@DateParam1 = '20130125'
		,@DateParam2 = '20130205'

Select	Col1
		,Col2
From	@tvTable
Where	Case @param1
			When 1 Then myDate1
			When 2 Then myDate2
			Else myDate3
		END Between @DateParam1 And @DateParam2

Edit: yay I can post now.