CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Kishan Gajjar
Kishan Gajjar

Reputation: 1138

Need help in dynamic query with IN Clause

I have 1 Table which is named as ProductMaster.Another Table which is VendorMaster. Now i want to find all the products from specified Vendormaster.

So i want to use IN Clause of SQL Server.

I will pass VendorName in the comma separated format. e.g. HP,LENOVO

I am using a function "f_split" to split a string with comma(,) as a separator.

I have to generate dynamic query as i want to use customized where clause for different parameters.

So please tell me how can i do this?

Upvotes: 0

Views: 966

Answers (3)

podiluska
podiluska

Reputation: 51494

If your verdor name is 

 declare @in varchar(100)
 select @in = 'HP,LENOVO'

You can use dynamic SQL

 declare @sql nvarchar(1000)
 select @sql = 'select * from yourtable where yourfield in ('+@in +')'
 exec sp_executesql @sql

or you can make your split function return a table

 select * 
 from yourtable
     inner join dbo.f_Split(@in) f 
     on yourtable.yourfield =f.entry

The second is much preferable due to its protection from SQL injection type attacks

Upvotes: 2

Preet Sangha
Preet Sangha

Reputation: 65516

You are better off using a join and nullable parameters individually

consider this table1 id joins on table 2 id, and the where clause can use paramter p1 for col1 or p2 for col2 etc...

select * 
from table1 t1
inner join table2 t2 on t2.id = t1.id -- join the tables
where 1=1
and t2.col1 = isnull(p1, t2.col1)
and t2.col2 = isnull(p2, t2.col2)
.
. -- etc...
.

If the p1 is null then you get a test of t2.col1 = t2.col1 which effectively dismisses p1 from the where clause.

Upvotes: 1

Darek
Darek

Reputation: 4797

You could just use

SELECT * FROM ProductMaster WHERE VendorId IN (
  SELECT DISTINCT VendorId FROM VendorMaster 
  WHERE PATINDEX('%'+VendorName+'%',@VendorNameCommaSeparated)>0
)

Upvotes: 0

Related Questions