Need help with a dynamic query using the IN clause

Question

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

So, I want to use the SQL Server IN section.

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

I use the "f_split" function to separate the semicolon line (,) as a separator.

I need to create a dynamic query as I want to use the customized where clause for different parameters.

So please tell me how can I do this?

Answer 1

If your name is faith

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 force the split function to return the table

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

The second is preferable because of its protection against SQL injection attacks.

Answer 2

You are better off using merge and null options individually

consider that this table1 identifier connects to table 2 identifier, and where clause can use p1 parameter 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 p1 is null, you get the test t2.col1 = t2.col1, which effectively rejects p1 from the where clause.

Answer 3

You can just use

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