Avoid Dynamic SQL when u want to use a variable in IN clause

When we want to filter the result set using IN clause. We end up having dynamic SQL.
The scenario goes like this.

Basic Query:
select
* --(writing * is not a god practice write down column names)
from
employee
where
employee_id in (100,10001,11560)

If the give employee ids come thru a variable(viz as input param to SP). U can get the result using dynamic SQL as below.

@strInput varchar(200)
set @strInput = '100,10001,11560'

@strQuery varchar(1000)
set @strQuery = 'select * from employee where employee_id in (' + @strInput + ')'

exec(@strQuery)

We can achieve the same with out dynamic SQL. The query below will address the same.

select * from employee where charindex(','+cast(employee_id as varchar)+',',@strInput) > 0


Note:
we need to convert the employee_id to varchar so that we can ap(pre)pend ',' to the id.
Why we need to add ',' to employee_id column?. (Think your self).
The reason is if u have employee_id's as 1,11,1111 then without ',' will give wrong results.

Hope you all appreciate the usage of charindex. More in the next article. Njoy reading.

Surendra