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.