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.