Use of IN in Dynamic SQL Query

Today I am going to explain How to use Dynamic sql query using “IN” keyword.

It is very easy to use IN query normally. But when you use IN in dynamic sql query. Its bit typical to use . I am writing down some queries for table creation and data insertion.

Create table #TBL_PINDETAILS

(

PinCode varchar(6),

AreaCovered varchar(500)

)

Insert Into #TBL_PINDETAILS values('110001','New Delhi HO, Shastri Bhawan')

Insert Into #TBL_PINDETAILS values('110002','Indraprastha HPO, Ajmeri Gate Extn')

Insert Into #TBL_PINDETAILS values('110003','C G O Complex, Delhi High Court')

Insert Into #TBL_PINDETAILS values('110004','Rashtrapati Bhawan')

If you run normal IN query

select * from #TBL_PINDETAILS where Pincode in('110001','110002','110003')

Result

110001	New Delhi HO, Shastri Bhawan
110002	Indraprastha HPO, Ajmeri Gate Extn
110003	C G O Complex, Delhi High Court

It gives proper result. Now, suppose I want to use above query with stored procedure and pass pincodes through parameter.

If you run IN using parameter-

declare @Query varchar(max)

declare @Param varchar(max)

set @Param='110001,110002'

Set @Query=(select * from #TBL_PINDETAILS where Pincode in(@Param))

select (@Query)

Result- NULL

Sql server doesnot understand @param as '110001,110002'
In this condition you have to make @Query dynamic .Like -

declare @Query varchar(max)

declare @Param varchar(max)

set @Param='''110001'',''110002'''

Set @Query='(select * from #TBL_PINDETAILS where Pincode in('+@Param+'))'

select (@Query)

Now it gives proper query-

(select * from #TBL_PINDETAILS where Pincode in('110001','110002'))

Result

110001	New Delhi HO, Shastri Bhawan
110002	Indraprastha HPO, Ajmeri Gate Extn

If there is any mistake in above concept please let me know in comments.