Simulation of IN clause using CASE statement

Submit Blog | Blogs Home |

Declare @t table(Val int, name varchar(100))
Insert into @t select 1,'name1' union all select 1, 'name2' union all select 2,'name3'
union all select 3,'name4'

select *
from @t 
where val in (1,2)

The output is as under

Val	name
1	name1
1	name2
2	name3

Now we will use CASE statement to simulate the IN clause as under

declare @type int  = 1

select *
from @t 
where val  = case when @type =1 then 1  end or
	  val  = case when @type =1 then 2  end or
	  val  = case when @type =2 then 3  end

Another way

declare @type int  = 1

select *
from @t  as x
where  case when @type = 1 and x.val in (1,2) then 1
	    when @type = 2 AND x.val in (3) then 1
       else 0   
       end = 1 

Another way

declare @type int  = 1

select *
from @t  as x
where  @type = case when x.val in (1,2) then 1 
		    when x.val in (3) then 2 
	       end 

We will receive the same output.Hope this helps