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

How the word 'Interface' came into OOP terminology

I thought of framing the question as (why the name for Interface is interface in Object Oriented Programming) :-). What ever, Just keep OOP aside and think what is an 'interface'?. What is the interface to your home/room/office?.

Interface is nothing but the way an object get exposed to the external world. That means you can play around with any object by its interface only. Now come to OOPs. U can play around with an object/class using its variables and its methods only(Is it Not?). Therefore interface to(of) a class is nothing but a its(class's) variables and method signatures.

Now come to Interface in OOPs..Afterall interface is also a class but it just consist only the variables and method signatures. Hence A class which just contains the details of its interface is called Interface.