在使用 IN 子句进行动态查询时需要帮助 [英] Need help in dynamic query with IN Clause

问题描述

我有一张名为 ProductMaster 的表.另一个名为 VendorMaster 的表.现在我想从指定的 Vendormaster 中查找所有产品.

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.

所以我想使用 SQL Server 的 IN 子句.

So i want to use IN Clause of SQL Server.

我将以逗号分隔的格式传递 VendorName.例如惠普、联想

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

我正在使用函数 "f_split" 以逗号 (,) 作为分隔符分割字符串.

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

我必须生成动态查询，因为我想对不同的参数使用自定义的 where 子句.

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?

推荐答案

如果您的 verdor name 是

If your verdor name is

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

您可以使用动态 SQL

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

第二种更可取，因为它可以防止 SQL 注入类型的攻击

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

这篇关于在使用 IN 子句进行动态查询时需要帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！