[SOLVED] in operator inside if condition and where clause in sql server

in operator inside if condition and where clause in sql server

I have to get same schema but different data, based on the where condition. Below is my query. I am trying to apply CASE statement inside where condition. The main problem is to handle in operator here. I don't want to apply IF condition on whole block as my original select statement is very big. I am okay if there is any other way than using CASE statement

      --@input is an outside variable

      select col1, col2, col3, col4, col5
      from table
      where col6 in 
      case where @input = 'web' then (18, 19, 20, 22)
      case where @input = 'net' then (37, 69, 26, 25)
      case where @input = 'com' then (55, 66, 46, 27)
      else (55, 46, 46, 17)
      end

Solution

Just use regular boolean expressions:

where (@input = 'web' and col6 in (18, 19, 20, 22)) or
      (@input = 'net' and col6 in (37, 69, 26, 25)) or
      (@input = 'com' and col6 in (55, 66, 46, 27)) or
      (@input not in ('web', 'net', 'com') and col6 in (55, 46, 46, 17))