none
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.

    Wednesday, January 23, 2013 7:03 PM

Answers

  • 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.

    • Edited by dgjohnson Wednesday, January 23, 2013 7:22 PM
    • Marked as answer by Sandy Malhothra Wednesday, January 23, 2013 7:45 PM
    Wednesday, January 23, 2013 7:22 PM

All replies

  • 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

    Wednesday, January 23, 2013 7:07 PM
  • 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.

    • Edited by dgjohnson Wednesday, January 23, 2013 7:22 PM
    • Marked as answer by Sandy Malhothra Wednesday, January 23, 2013 7:45 PM
    Wednesday, January 23, 2013 7:22 PM