SQL Where IF statement with multiple conditions-CodePudding

/* I am working in SQL Server Report Builder and I have a Where statement where a parameter equals to a value, check another parameter then do something, else do something else. Below is an example: */

where 
case when @Parameter1 = 'Value' then 
    when @Parameter2 = True then
       Date >= @BegDate and
       Date <= @EndDate and
       totext(Field) = @Parameter1 and
      Field2 = 'Value2'
    Else
       totext(Field) = @Parameter1 and
        Field2 = 'Value2' 
Case when @Parameter1 = 'Value3' then
        Field3 = ToNumber(@Parameter3) and
        Field2 = 'Value2';

CodePudding user response：

You don't need CASE here, which returns a scalar value. Just use normal boolean logic

WHERE (
       (
             @Parameter1 = 'Value'
         AND @Parameter2 = 'True'
         AND Date >= @BegDate
         AND Date <= @EndDate
         AND totext(Field) = @Parameter1
         AND Field2 = 'Value2'
        )
  OR
        ( @Parameter1 = 'Value'
         AND totext(Field) = @Parameter1
         AND Field2 = 'Value2'
        )
  OR
        ( @Parameter1 = 'Value3'
         AND FIeld3 = ToNumber(@Parameter3)
         AND Field2 = 'Value2'
        )
  )

You can obviously simplify this by pulling some conditions to the outside of the OR

CodePudding user response：

You need to rewrite your case so that it "returns" something that you then check for. I'm not sure where exactly what your logic is because your CASEs are incorrectly nested so it's hard to read, but this should give you an idea

WHERE CASE -- first set of conditions
        WHEN @Parameter1 = 'Value'
         AND @Parameter2 = True
         AND Date >= @BegDate
         AND Date <= @EndDate
         AND totext(Field) = @Parameter1
         AND Field2 = 'Value2'
        THEN 1 -- return 1 when the first condition is met
  
        -- Second set of conditions      
        WHEN @Parameter1 = 'Value' -- but not @Parameter2 = True, that'd have been matched above
         AND totext(Field) = @Parameter1
         AND Field2 = 'Value2'
        THEN 1`
  
        -- Third set of conditions
        WHEN @Parameter1 = 'Value3'
         AND FIeld3 = ToNumber(@Parameter3)
         AND Field2 = 'Value2'
        THEN 1
        -- otherwise we're returning 0
        ELSE 0
    END = 1 -- here we're checking if the CASE returns a 1