您可以选择几条路线
- The "classic" 传入一个分隔字符串参数,并使用数据库中用户定义的表值函数将字符串转换为表。然后您可以联接该表进行过滤。
类似的东西(这将返回一个表INT
数据类型,只需稍微更改代码即可VARCHAR
CREATE function [dbo].[csl_to_table] ( @list nvarchar(MAX) )
RETURNS @list_table TABLE ([id] INT)
AS
BEGIN
DECLARE @index INT,
@start_index INT,
@id INT
SELECT @index = 1
SELECT @start_index = 1
WHILE @index <= DATALENGTH(@list)
BEGIN
IF SUBSTRING(@list,@index,1) = ','
BEGIN
SELECT @id = CAST(SUBSTRING(@list, @start_index,
@index - @start_index ) AS INT)
INSERT @list_table ([id]) VALUES (@id)
SELECT @start_index = @index + 1
END
SELECT @index = @index + 1
END
SELECT @id = CAST(SUBSTRING(@list, @start_index,
@index - @start_index ) AS INT)
INSERT @list_table ([id]) VALUES (@id)
RETURN
END
- 您可以在 .NET 代码中使用循环来构建
IN
条款。但请记住,您最多只能使用 256 个参数 (IIRC)。
像这样的东西(啦这个答案 https://stackoverflow.com/questions/337704/parameterizing-a-sql-in-clause/337792#337792)
string[] tags = new string[] { "Subway","A & W","Pizzahut" };
string cmdText =
"SELECT CompanyId FROM CompanyTable WHERE CompanyName IN ({0})";
string[] paramNames = tags.Select(
(s, i) => "@tag" + i.ToString()
).ToArray();
string inClause = string.Join(",", paramNames);
using (SqlCommand cmd = new SqlCommand(string.Format(cmdText, inClause))) {
for(int i = 0; i < paramNames.Length; i++) {
cmd.Parameters.AddWithValue(paramNames[i], tags[i]);
}
}
- 根据 SQL 的版本,可以使用XML 数据类型参数 http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing-lists-to-sql-server-2005-with-xml-parameters.aspx(SQL Server 2005 及以上)传递多个参数或一个表值参数 http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters(SQL Server 2008 及以上版本)