Wednesday, June 14, 2006

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:
* --(writing * is not a god practice write down column names)
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 + ')'


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

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.



jasonmonk said...

Mayya..nuvu too much..chaala dynamic ga rasavu.!!

Anonymous said...

This site is one of the best I have ever seen, wish I had one like this.

Anonymous said...

I find some information here.