参数化SQL IN子句

编程入门行业动态更新时间:2024-10-04 17:17:38

参数化SQL IN<a href=https://www.elefans.com/category/jswz/34/1713883.html style= 子句"/>

参数化SQL IN子句

我该如何参数化包含一个带有可变数量参数的IN子句的查询，就像这样？

SELECT * FROM Tags 
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC

在此查询中，参数的数量可以为1到5之间的任意值。

我不希望为此（或XML）使用专用的存储过程，但是如果有一些特定于SQL Server 2008的优雅方法，我可以接受。

#1楼

这是一种重新创建要在查询字符串中使用的本地表的技术。这样做可以消除所有解析问题。

字符串可以用任何语言构建。在此示例中，我使用了SQL，因为这是我要解决的原始问题。我需要一种干净的方法来动态地将表数据传入一个字符串中，以便稍后执行。

使用用户定义的类型是可选的。创建类型只能创建一次，并且可以提前完成。否则，只需将完整表类型添加到字符串中的声明中即可。

通用模式易于扩展，可用于传递更复杂的表。

-- Create a user defined type for the list.
CREATE TYPE [dbo].[StringList] AS TABLE([StringValue] [nvarchar](max) NOT NULL
)-- Create a sample list using the list table type.
DECLARE @list [dbo].[StringList]; 
INSERT INTO @list VALUES ('one'), ('two'), ('three'), ('four')-- Build a string in which we recreate the list so we can pass it to exec
-- This can be done in any language since we're just building a string.
DECLARE @str nvarchar(max);
SET @str = 'DECLARE @list [dbo].[StringList]; INSERT INTO @list VALUES '-- Add all the values we want to the string. This would be a loop in C++.
SELECT @str = @str + '(''' + StringValue + '''),' FROM @list-- Remove the trailing comma so the query is valid sql.
SET @str = substring(@str, 1, len(@str)-1)-- Add a select to test the string.
SET @str = @str + '; SELECT * FROM @list;'-- Execute the string and see we've pass the table correctly.
EXEC(@str)

#2楼

使用以下存储过程。它使用自定义拆分功能，可在此处找到。

 create stored procedure GetSearchMachingTagNames @PipeDelimitedTagNames varchar(max), @delimiter char(1) as  beginselect * from Tags where Name in (select data from [dbo].[Split](@PipeDelimitedTagNames,@delimiter) end

#3楼

如果您使用的是SQL Server 2008或更高版本，则可以使用表值参数。

如果您不幸遇到SQL Server 2005的问题，可以添加这样的CLR函数，

[SqlFunction(DataAccessKind.None,IsDeterministic = true,SystemDataAccess = SystemDataAccessKind.None,IsPrecise = true,FillRowMethodName = "SplitFillRow",TableDefinintion = "s NVARCHAR(MAX)"]
public static IEnumerable Split(SqlChars seperator, SqlString s)
{if (s.IsNull)return new string[0];return s.ToString().Split(seperator.Buffer);
}public static void SplitFillRow(object row, out SqlString s)
{s = new SqlString(row.ToString());
}

您可以这样使用

declare @desiredTags nvarchar(MAX);
set @desiredTags = 'ruby,rails,scruffy,rubyonrails';select * from Tags
where Name in [dbo].[Split] (',', @desiredTags)
order by Count desc

#4楼

如果我们在IN子句中存储了以逗号（，）分隔的字符串，则可以使用charindex函数获取值。如果使用.NET，则可以使用SqlParameters进行映射。

DDL脚本：

CREATE TABLE Tags([ID] int, [Name] varchar(20))
;INSERT INTO Tags([ID], [Name])
VALUES(1, 'ruby'),(2, 'rails'),(3, 'scruffy'),(4, 'rubyonrails')
;

T-SQL：

DECLARE @Param nvarchar(max)SET @Param = 'ruby,rails,scruffy,rubyonrails'SELECT * FROM Tags
WHERE CharIndex(Name,@Param)>0

您可以在.NET代码中使用上述语句，并使用SqlParameter映射参数。

提琴手演示

编辑：使用以下脚本创建名为SelectedTags的表。

DDL脚本：

Create table SelectedTags
(Name nvarchar(20));INSERT INTO SelectedTags values ('ruby'),('rails')

T-SQL：

DECLARE @list nvarchar(max)
SELECT @list=coalesce(@list+',','')+st.Name FROM SelectedTags stSELECT * FROM Tags
WHERE CharIndex(Name,@Param)>0

#5楼

这是另一种选择。只需将逗号分隔的列表作为字符串参数传递给存储过程，然后：

CREATE PROCEDURE [dbo].[sp_myproc]@UnitList varchar(MAX) = '1,2,3'
AS
select column from table
where ph.UnitID in (select * from CsvToInt(@UnitList))

和功能：

CREATE Function [dbo].[CsvToInt] ( @Array varchar(MAX))
returns @IntTable table
(IntValue int)
AS
begindeclare @separator char(1)set @separator = ','declare @separator_position intdeclare @array_value varchar(MAX)set @array = @array + ','while patindex('%,%' , @array) <> 0beginselect @separator_position = patindex('%,%' , @array)select @array_value = left(@array, @separator_position - 1)Insert @IntTableValues (Cast(@array_value as int))select @array = stuff(@array, 1, @separator_position, '')endreturn
end

#6楼

这是这个问题的另一个答案。

（新版本发布于6/4/13）。

    private static DataSet GetDataSet(SqlConnectionStringBuilder scsb, string strSql, params object[] pars){var ds = new DataSet();using (var sqlConn = new SqlConnection(scsb.ConnectionString)){var sqlParameters = new List<SqlParameter>();var replacementStrings = new Dictionary<string, string>();if (pars != null){for (int i = 0; i < pars.Length; i++){if (pars[i] is IEnumerable<object>){List<object> enumerable = (pars[i] as IEnumerable<object>).ToList();replacementStrings.Add("@" + i, String.Join(",", enumerable.Select((value, pos) => String.Format("@_{0}_{1}", i, pos))));sqlParameters.AddRange(enumerable.Select((value, pos) => new SqlParameter(String.Format("@_{0}_{1}", i, pos), value ?? DBNull.Value)).ToArray());}else{sqlParameters.Add(new SqlParameter(String.Format("@{0}", i), pars[i] ?? DBNull.Value));}}}strSql = replacementStrings.Aggregate(strSql, (current, replacementString) => current.Replace(replacementString.Key, replacementString.Value));using (var sqlCommand = new SqlCommand(strSql, sqlConn)){if (pars != null){sqlCommand.Parameters.AddRange(sqlParameters.ToArray());}else{//Fail-safe, just in case a user intends to pass a single null parametersqlCommand.Parameters.Add(new SqlParameter("@0", DBNull.Value));}using (var sqlDataAdapter = new SqlDataAdapter(sqlCommand)){sqlDataAdapter.Fill(ds);}}}return ds;}

干杯。

#7楼

另一种可能的解决方案是向存储过程传递可变数量的参数，而不是传递包含您要使用的名称的单个字符串，而是通过用'<>'包围它们来使其唯一。然后使用PATINDEX查找名称：

SELECT * 
FROM Tags 
WHERE PATINDEX('%<' + Name + '>%','<jo>,<john>,<scruffy>,<rubyonrails>') > 0

#8楼

我使用投票结果最简洁的答案：

List<SqlParameter> parameters = tags.Select((s, i) => new SqlParameter("@tag" + i.ToString(), SqlDbType.NVarChar(50)) { Value = s}).ToList();var whereCondition = string.Format("tags in ({0})", String.Join(",",parameters.Select(s => s.ParameterName)));

它确实两次遍历标签参数。但这在大多数时候都没有关系（这不是您的瓶颈；如果是，请展开循环）。

如果您对性能真的很感兴趣，并且不想在循环中重复两次，则可以使用以下更漂亮的版本：

var parameters = new List<SqlParameter>();
var paramNames = new List<string>();
for (var i = 0; i < tags.Length; i++)  
{var paramName = "@tag" + i;//Include size and set value explicitly (not AddWithValue)//Because SQL Server may use an implicit conversion if it doesn't know//the actual size.var p = new SqlParameter(paramName, SqlDbType.NVarChar(50) { Value = tags[i]; } paramNames.Add(paramName);parameters.Add(p);
}var inClause = string.Join(",", paramNames);

#9楼

我认为这是静态查询不是解决之道的情况。动态构建in子句的列表，转义单引号，并动态构建SQL。在这种情况下，由于列表太小，您可能看不到任何方法有什么不同，但是最有效的方法实际上是完全按照您的帖子中编写的方式发送SQL。我认为，以最有效的方式编写代码是一个好习惯，而不是去做最漂亮的代码，或者认为动态构建SQL是一种不好的做法。

我已经看到，在许多参数变大的情况下，拆分函数比查询本身执行所需的时间更长。我会考虑的唯一选择是SQL 2008中具有表值参数的存储过程，尽管在您的情况下这可能会更慢。如果在TVP的主键上进行搜索，则TVP可能只会对大型列表更快，因为SQL仍将为该列表建立一个临时表（如果列表很大）。除非您进行测试，否则您将无法确定。

我还看到了存储过程，该存储过程具有500个参数，默认值为null，并且具有WHERE Column1 IN（@ Param1，@ Param2，@ Param3，...，@ Param500）。这导致SQL建立一个临时表，进行排序/区分，然后执行表扫描而不是索引查找。实际上，这是通过参数化该查询来执行的操作，尽管规模很小，不会产生明显的变化。我强烈建议您不要在IN列表中包含NULL，因为将其更改为NOT IN不会起到预期的作用。您可以动态地构建参数列表，但是唯一显而易见的是对象将为您转义单引号。由于对象必须解析查询以找到参数，因此该方法在应用程序端也稍慢一些。在SQL上，它可能会更快，也可能不会更快，因为参数化查询调用sp_prepare，sp_execute执行查询的次数相同，其次是sp_unprepare。

对存储过程或参数化查询重复使用执行计划可能会提高性能，但会将您锁定在一个由执行的第一个查询确定的执行计划中。在许多情况下，这对于后续查询可能不太理想。在您的情况下，重用执行计划可能会有所帮助，但由于示例是一个非常简单的查询，因此可能根本没有任何区别。

悬崖笔记：

对于您而言，您可以执行任何操作，无论是在列表中使用固定数量的项目进行参数化（如果未使用，则为null），动态构建带有或不带有参数的查询，或使用具有表值参数的存储过程都不会有太大的不同。但是，我的一般建议如下：

您的案例/简单查询，只需几个参数：

动态SQL，如果测试显示更好的性能，则可能带有参数。

具有可重复使用的执行计划的查询，只需更改参数或查询比较复杂即可多次调用：

具有动态参数的SQL。

清单较大的查询：

具有表值参数的存储过程。如果列表可能有很大差异，请在存储过程中使用WITH RECOMPILE，或者仅使用不带参数的动态SQL为每个查询生成新的执行计划。

#10楼

默认情况下，我会通过将表值函数（从字符串返回表）传递给IN条件来实现此目的。

这是UDF的代码（我是从Stack Overflow某处获得的，我现在找不到源）

CREATE FUNCTION [dbo].[Split] (@sep char(1), @s varchar(8000))
RETURNS table
AS
RETURN (WITH Pieces(pn, start, stop) AS (SELECT 1, 1, CHARINDEX(@sep, @s)UNION ALLSELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)FROM PiecesWHERE stop > 0)SELECT SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS sFROM Pieces)

一旦掌握了这一点，您的代码将像下面这样简单：

select * from Tags 
where Name in (select s from dbo.split(';','ruby;rails;scruffy;rubyonrails'))
order by Count desc

除非您有一个很长的字符串，否则它应该与表索引一起使用。

如果需要，您可以将其插入到临时表中，对其进行索引，然后运行联接...

#11楼

我认为，解决此问题的最佳来源是此网站上发布的内容：

Syscomments。迪纳卡·内蒂（Dinakar Nethi）

CREATE FUNCTION dbo.fnParseArray (@Array VARCHAR(1000),@separator CHAR(1))
RETURNS @T Table (col1 varchar(50))
AS 
BEGIN--DECLARE @T Table (col1 varchar(50))  -- @Array is the array we wish to parse-- @Separator is the separator charactor such as a commaDECLARE @separator_position INT -- This is used to locate each separator characterDECLARE @array_value VARCHAR(1000) -- this holds each array value as it is returned-- For my loop to work I need an extra separator at the end. I always look to the-- left of the separator character for each array valueSET @array = @array + @separator-- Loop through the string searching for separtor charactersWHILE PATINDEX('%' + @separator + '%', @array) <> 0 BEGIN-- patindex matches the a pattern against a stringSELECT @separator_position = PATINDEX('%' + @separator + '%',@array)SELECT @array_value = LEFT(@array, @separator_position - 1)-- This is where you process the values passed.INSERT into @T VALUES (@array_value)    -- Replace this select statement with your processing-- @array_value holds the value of this element of the array-- This replaces what we just processed with and empty stringSELECT @array = STUFF(@array, 1, @separator_position, '')ENDRETURN 
END

采用：

SELECT * FROM dbo.fnParseArray('a,b,c,d,e,f', ',')

致谢：Dinakar Nethi

#12楼

在SQL Server 2016+中，另一种可能性是使用OPENJSON函数。

这种方法是在OPENJSON中发布的，这是通过ID列表选择行的最佳方法之一。

下面是一个完整的示例

CREATE TABLE dbo.Tags(Name  VARCHAR(50),Count INT)INSERT INTO dbo.Tags
VALUES      ('VB',982), ('ruby',1306), ('rails',1478), ('scruffy',1), ('C#',1784)GOCREATE PROC dbo.SomeProc
@Tags VARCHAR(MAX)
AS
SELECT T.*
FROM   dbo.Tags T
WHERE  T.Name IN (SELECT J.Value COLLATE Latin1_General_CI_ASFROM   OPENJSON(CONCAT('[', @Tags, ']')) J)
ORDER  BY T.Count DESCGOEXEC dbo.SomeProc @Tags = '"ruby","rails","scruffy","rubyonrails"'DROP TABLE dbo.Tags

#13楼

您可以将参数作为字符串传递

所以你有字符串

DECLARE @tagsSET @tags = ‘ruby|rails|scruffy|rubyonrails’select * from Tags 
where Name in (SELECT item from fnSplit(@tags, ‘|’))
order by Count desc

然后，您要做的就是将字符串作为1参数传递。

这是我使用的分割功能。

CREATE FUNCTION [dbo].[fnSplit](@sInputList VARCHAR(8000) -- List of delimited items, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0BEGINSELECT@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))IF LEN(@sItem) > 0INSERT INTO @List SELECT @sItemENDIF LEN(@sInputList) > 0INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END

#14楼

我将传递一个表类型参数（因为它是SQL Server 2008 ），然后执行where exists或内部联接。您还可以使用XML，使用sp_xml_preparedocument ，然后甚至索引该临时表。

#15楼

对于像这样的可变数量的参数，我知道的唯一方法是显式生成SQL或执行某些操作，其中涉及使用所需项填充临时表并针对临时表进行联接。

#16楼

这很麻烦，但是如果您保证至少有一个，则可以执行以下操作：

SELECT ......WHERE tag IN( @tag1, ISNULL( @tag2, @tag1 ), ISNULL( @tag3, @tag1 ), etc. )

使用SQL Server可以轻松优化拥有IN（'tag1'，'tag2'，'tag1'，'tag1'，'tag1'）的位置。另外，您可以获得直接索引搜索

#17楼

您可以参数化每个值，例如：

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
string cmdText = "SELECT * FROM Tags WHERE Name 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]);}
}

这会给你：

cmd.CommandText = "SELECT * FROM Tags WHERE Name IN (@tag0, @tag1, @tag2, @tag3)"
cmd.Parameters["@tag0"] = "ruby"
cmd.Parameters["@tag1"] = "rails"
cmd.Parameters["@tag2"] = "scruffy"
cmd.Parameters["@tag3"] = "rubyonrails"

不，这对SQL注入不开放。唯一注入到CommandText中的文本不是基于用户输入的。它仅基于硬编码的“ @tag”前缀和数组的索引。索引将始终是整数，不是用户生成的，并且是安全的。

用户输入的值仍然填充到参数中，因此那里没有漏洞。

编辑：

除了注入问题外，请注意构造命令文本以容纳可变数量的参数（如上所述）会阻碍SQL Server利用缓存查询的能力。最终结果是，您几乎肯定首先失去了使用参数的价值（与仅将谓词字符串插入SQL本身相反）。

并不是说缓存的查询计划不是很有价值，但是IMO这个查询还不够复杂，无法从中受益良多。尽管编译成本可能接近（甚至超过）执行成本，但您仍在谈论毫秒。

如果您有足够的RAM，我希望SQL Server可能也会为通用的参数计数缓存一个计划。我想您总是可以添加五个参数，并且将未指定的标签设为NULL-查询计划应该相同，但是对我来说似乎很难看，而且我不确定是否值得进行微优化（不过，堆栈溢出-可能非常值得）。

而且，SQL Server 7和更高版本将自动对查询进行参数化，因此从性能的角度来看，使用参数并不是真正必要的，但是从安全的角度来看，使用参数至关重要 （尤其是对于用户输入的数据而言）。

#18楼

这是我使用的一种快捷方法：

SELECT * FROM Tags
WHERE '|ruby|rails|scruffy|rubyonrails|'
LIKE '%|' + Name + '|%'

因此，这是C＃代码：

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
const string cmdText = "select * from tags where '|' + @tags + '|' like '%|' + Name + '|%'";using (SqlCommand cmd = new SqlCommand(cmdText)) {cmd.Parameters.AddWithValue("@tags", string.Join("|", tags);
}

两个警告：

表现很糟糕。 LIKE "%...%"查询未编制索引。
确保您没有任何| ，空白或空标签，否则将无法使用

还有一些其他方法可以使某些人认为它更干净，因此请继续阅读。

#19楼

对于SQL Server 2008，可以使用表值参数。这有点费力，但可以说比我的其他方法干净。

首先，您必须创建一个类型

CREATE TYPE dbo.TagNamesTableType AS TABLE ( Name nvarchar(50) )

然后，您的ADO.NET代码如下所示：

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
cmd.CommandText = "SELECT Tags.* FROM Tags JOIN @tagNames as P ON Tags.Name = P.Name";// value must be IEnumerable<SqlDataRecord>
cmd.Parameters.AddWithValue("@tagNames", tags.AsSqlDataRecord("Name")).SqlDbType = SqlDbType.Structured;
cmd.Parameters["@tagNames"].TypeName = "dbo.TagNamesTableType";// Extension method for converting IEnumerable<string> to IEnumerable<SqlDataRecord>
public static IEnumerable<SqlDataRecord> AsSqlDataRecord(this IEnumerable<string> values, string columnName) {if (values == null || !values.Any()) return null; // Annoying, but SqlClient wants null instead of 0 rowsvar firstRecord = values.First();var metadata = SqlMetaData.InferFromValue(firstRecord, columnName);return values.Select(v => {var r = new SqlDataRecord(metadata);r.SetValues(v);return r;});
}

#20楼

我使用过一次，这可能是一种半讨厌的方式，它相当有效。

根据您的目标，它可能会有用。

用一列创建一个临时表 。
INSERT每个查找值插入该列。
除了使用IN ，您还可以使用标准的JOIN规则。（灵活性++）

这在您可以执行的操作中增加了一点灵活性，但是它更适合于您要查询的表很大，索引很好并且您希望多次使用参数化列表的情况。省去了执行两次并手动完成所有清洁的麻烦。

我从来没有完全了解它的速度，但是在我的情况下是必需的。

#21楼

我们有创建表变量的函数，您可以将其加入其中：

ALTER FUNCTION [dbo].[Fn_sqllist_to_table](@list  AS VARCHAR(8000),@delim AS VARCHAR(10))
RETURNS @listTable TABLE(Position INT,Value    VARCHAR(8000))
ASBEGINDECLARE @myPos INTSET @myPos = 1WHILE Charindex(@delim, @list) > 0BEGININSERT INTO @listTable(Position,Value)VALUES     (@myPos,LEFT(@list, Charindex(@delim, @list) - 1))SET @myPos = @myPos + 1IF Charindex(@delim, @list) = Len(@list)INSERT INTO @listTable(Position,Value)VALUES     (@myPos,'')SET @list = RIGHT(@list, Len(@list) - Charindex(@delim, @list))ENDIF Len(@list) > 0INSERT INTO @listTable(Position,Value)VALUES     (@myPos,@list)RETURNEND

所以：

@Name varchar(8000) = null // parameter for search values    select * from Tags 
where Name in (SELECT value From fn_sqllist_to_table(@Name,',')))
order by Count desc

#22楼

在ColdFusion中，我们只做：

<cfset myvalues = "ruby|rails|scruffy|rubyonrails"><cfquery name="q">select * from sometable where values in <cfqueryparam value="#myvalues#" list="true"></cfquery>

#23楼

在SQL Server 2016+您可以使用STRING_SPLIT函数：

DECLARE @names NVARCHAR(MAX) = 'ruby,rails,scruffy,rubyonrails';SELECT * 
FROM Tags
WHERE Name IN (SELECT [value] FROM STRING_SPLIT(@names, ','))
ORDER BY Count DESC;

要么：

DECLARE @names NVARCHAR(MAX) = 'ruby,rails,scruffy,rubyonrails';SELECT t.*
FROM Tags t
JOIN STRING_SPLIT(@names,',')ON t.Name = [value]
ORDER BY Count DESC;

LiveDemo

公认的答案当然会起作用，这是解决问题的方法之一，但这是反模式的。

E.按值列表查找行

这可以替代常见的反模式，例如在应用程序层或Transact-SQL中创建动态SQL字符串，或者使用LIKE运算符：
 SELECT ProductId, Name, Tags FROM Product WHERE ',1,2,3,' LIKE '%,' + CAST(ProductId AS VARCHAR(20)) + ',%'; 

^{原始问题有要求SQL Server 2008 。} ^{因为这个问题经常被重复使用，所以我添加了这个答案作为参考。}

#24楼

我今天在播客上听到Jeff / Joel谈到这个问题（ 第34集 ，2008-12-16（MP3，31 MB），1小时03分38秒-1小时06分45秒），我以为我想起了Stack Overflow在使用LINQ to SQL ，但可能已被放弃。这在LINQ to SQL中也是一样。

var inValues = new [] { "ruby","rails","scruffy","rubyonrails" };var results = from tag in Tagswhere inValues.Contains(tag.Name)select tag;

而已。而且，是的，LINQ已经足够向后看，但是Contains子句对我而言似乎是额外向后的。当我必须对工作中的项目进行类似的查询时，我自然会尝试通过在本地数组和SQL Server表之间进行联接来以错误的方式执行此操作，从而弄清楚LINQ to SQL转换器将足够聪明来处理翻译莫名其妙。它没有，但是确实提供了描述性的错误消息，并向我指出了使用Contains 。

无论如何，如果在强烈推荐的LINQPad中运行此命令并运行此查询，则可以查看SQL LINQ提供程序生成的实际SQL。它将向您显示每个值被参数化为IN子句。

#25楼

IMHO的正确方法是将列表存储在一个字符串中（长度受DBMS支持）。唯一的技巧是（为了简化处理）我在字符串的开头和结尾都有一个分隔符（在我的示例中为逗号）。这个想法是“动态标准化”，将列表变成一个单列表，每个值包含一行。这可以让你转

在（ct1，ct2，ct3 ... ctn）中

变成一个

在（选择...）

或（我可能更喜欢的解决方案）常规联接，如果您只是添加一个“区别”以避免列表中重复值出现问题。

不幸的是，切片字符串的技术是特定于产品的。这是SQL Server版本：

 with qry(n, names) as(select len(list.names) - len(replace(list.names, ',', '')) - 1 as n,substring(list.names, 2, len(list.names)) as namesfrom (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' names) as listunion allselect (n - 1) as n,substring(names, 1 + charindex(',', names), len(names)) as namesfrom qrywhere n > 1)select n, substring(names, 1, charindex(',', names) - 1) dwarffrom qry;

Oracle版本：

 select n, substr(name, 1, instr(name, ',') - 1) dwarffrom (select n,substr(val, 1 + instr(val, ',', 1, n)) namefrom (select rownum as n,list.valfrom  (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' valfrom dual) listconnect by level < length(list.val) -length(replace(list.val, ',', ''))));

和MySQL版本：

select pivot.n,substring_index(substring_index(list.val, ',', 1 + pivot.n), ',', -1) from (select 1 as nunion allselect 2 as nunion allselect 3 as nunion allselect 4 as nunion allselect 5 as nunion allselect 6 as nunion allselect 7 as nunion allselect 8 as nunion allselect 9 as nunion allselect 10 as n) pivot,    (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' val) as list where pivot.n <  length(list.val) -length(replace(list.val, ',', ''));

（当然，“数据透视表”必须返回的行数与我们在列表中可以找到的最大项目数一样多）

#26楼

唯一的制胜法宝是不参加比赛。

您没有无限的可变性。只有有限的可变性。

在SQL中，您有一个像这样的子句：

and ( {1}==0 or b.CompanyId in ({2},{3},{4},{5},{6}) )

在C＃代码中，您可以执行以下操作：

  int origCount = idList.Count;if (origCount > 5) {throw new Exception("You may only specify up to five originators to filter on.");}while (idList.Count < 5) { idList.Add(-1); }  // -1 is an impossible valuereturn ExecuteQuery<PublishDate>(getValuesInListSQL, origCount,   idList[0], idList[1], idList[2], idList[3], idList[4]);

因此，基本上，如果计数为0，则没有过滤器，一切都会通过。如果计数大于0，则该值必须在列表中，但是该列表已用不可能的值填充为五个（这样，SQL仍然有意义）

有时，me脚的解决方案是唯一有效的解决方案。

#27楼

我有一个不需要UDF，XML的答案，因为IN接受一条select语句，例如SELECT * FROM Test where Data IN（从表中选择SELECT值）

您实际上只需要一种将字符串转换为表的方法。

这可以通过递归CTE或带有数字表（或Master..spt_value）的查询来完成

这是CTE版本。

DECLARE @InputString varchar(8000) = 'ruby,rails,scruffy,rubyonrails'SELECT @InputString = @InputString + ',';WITH RecursiveCSV(x,y) 
AS 
(SELECT x = SUBSTRING(@InputString,0,CHARINDEX(',',@InputString,0)),y = SUBSTRING(@InputString,CHARINDEX(',',@InputString,0)+1,LEN(@InputString))UNION ALLSELECT x = SUBSTRING(y,0,CHARINDEX(',',y,0)),y = SUBSTRING(y,CHARINDEX(',',y,0)+1,LEN(y))FROM RecursiveCSV WHERESUBSTRING(y,CHARINDEX(',',y,0)+1,LEN(y)) <> '' OR SUBSTRING(y,0,CHARINDEX(',',y,0)) <> ''
)
SELECT* 
FROM Tags
WHERE Name IN (select x FROM RecursiveCSV)
OPTION (MAXRECURSION 32767);

#28楼

如果从.NET调用，则可以使用Dapper点网：

string[] names = new string[] {"ruby","rails","scruffy","rubyonrails"};
var tags = dataContext.Query<Tags>(@"
select * from Tags 
where Name in @names
order by Count desc", new {names});

Dapper在这里进行思考，因此您不必这样做。当然，使用LINQ to SQL可能会发生类似的事情：

string[] names = new string[] {"ruby","rails","scruffy","rubyonrails"};
var tags = from tag in dataContext.Tagswhere names.Contains(tag.Name)orderby tag.Count descendingselect tag;

#29楼

也许我们可以在这里使用XML：

    declare @x xmlset @x='<items><item myvalue="29790" /><item myvalue="31250" /></items>';With CTE AS (SELECT x.item.value('@myvalue[1]', 'decimal') AS myvalueFROM @x.nodes('//items/item') AS x(item) )select * from YourTable where tableColumnName in (select myvalue from cte)

#30楼

最初的问题是“如何参数化查询...”

让我在这里声明，这不是对原始问题的答案 。其他好的答案中已经有一些示范。

话虽如此，继续并标记此答案，对其进行否决，将其标记为不是答案...做您认为正确的事情。

请参阅Mark Brackett的答案，以获取我（及其他231个）支持的首选答案。在他的答案中给出的方法允许1）有效使用绑定变量，以及2）可处理谓词。

选择的答案

我想在这里解决的是乔尔·斯波斯基（Joel Spolsky）的答案中给出的方法，即“选择”答案为正确答案。

乔尔·斯波斯基（Joel Spolsky）的做法很聪明。而且它合理地工作，在给定“正常”值的情况下，并且具有规范的边缘情况（例如NULL和空字符串），它将表现出可预测的行为和可预测的性能。对于特定的应用可能就足够了。

但是从广义上来说，我们还要考虑更晦涩的特殊情况，例如当“ Name列包含通配符（由LIKE谓词识别）时，我看到的最常用的通配符是% （百分号）。。因此，让我们现在在这里进行处理，然后再处理其他情况。

％字符的一些问题

考虑名称值'pe%ter' 。（对于此处的示例，我使用文字字符串值代替列名。）查询形式将返回名称值为''pe％ter'的行：

select ...where '|peanut|butter|' like '%|' + 'pe%ter' + '|%'

但是，如果搜索条件的顺序相反，则不会返回同一行：

select ...where '|butter|peanut|' like '%|' + 'pe%ter' + '|%'

我们观察到的行为有点奇怪。更改列表中搜索词的顺序会更改结果集。

几乎不用说，我们可能不希望pe%ter搭配花生酱，无论他多么喜欢。

暗角机箱

（是的，我会同意这是一个晦涩的案例。很可能不会进行测试。我们不会期望在列值中使用通配符。我们可能会假定该应用程序阻止存储该值。但是以我的经验，我很少见到数据库约束，该约束特别禁止在LIKE比较运算符右侧将字符或模式视为通配符。

修补孔

修补此漏洞的一种方法是转义%通配符。（对于不熟悉运算符上的转义子句的任何人，这是SQL Server文档的链接。

select ...where '|peanut|butter|'like '%|' + 'pe\%ter' + '|%' escape '\'

现在我们可以匹配文字％。当然，当我们有一个列名时，我们将需要动态转义通配符。我们可以使用REPLACE函数查找出现的%字符，并在每个字符前面插入反斜杠字符，如下所示：

select ...where '|pe%ter|'like '%|' + REPLACE( 'pe%ter' ,'%','\%') + '|%' escape '\'

这样就解决了％通配符的问题。几乎。

逃脱

我们认识到我们的解决方案带来了另一个问题。转义字符。我们看到，我们还需要对任何出现的转义字符本身进行转义。这次，我们使用！作为转义字符：

select ...where '|pe%t!r|'like '%|' + REPLACE(REPLACE( 'pe%t!r' ,'!','!!'),'%','!%') + '|%' escape '!'

下划线也是

现在，我们可以进行下一步，我们可以添加另一个REPLACE处理下划线通配符。只是为了好玩，这次，我们将$作为转义字符。

select ...where '|p_%t!r|'like '%|' + REPLACE(REPLACE(REPLACE( 'p_%t!r' ,'$','$$'),'%','$%'),'_','$_') + '|%' escape '$'

我更喜欢这种转义方法，因为它可以在Oracle和MySQL以及SQL Server中工作。（我通常使用\\反斜杠作为转义字符，因为这是我们在正则表达式中使用的字符。但是为什么要遵守约定！

那些讨厌的括号

SQL Server还通过将通配符括在方括号[] ，将通配符视为文字。因此，至少在SQL Server中，我们还没有完成修复。由于成对的括号具有特殊含义，因此我们也需要对它们进行转义。如果我们设法正确地逃脱了括号，那么至少我们不必打扰连字符-以及括号内的克拉^ 。我们可以保留括号内的所有%和_字符，因为我们将基本上禁用括号的特殊含义。

寻找匹配的括号对并不难。这比处理单例％和_的出现要困难一些。（请注意，仅转义所有出现的方括号是不够的，因为单身方括号被认为是文字，并且不需要转义。逻辑变得比我不运行更多测试用例就更模糊了）

内联表达式变得凌乱

SQL中的内联表达式变得越来越长和难看。我们可能可以使它起作用，但是天堂可以帮助落后的可怜灵魂，并必须对其进行解密。我非常喜欢内联表达式，我倾向于在这里不使用它，主要是因为我不想留下评论来解释混乱的原因并为此道歉。

一个功能在哪里？

好的，因此，如果我们不将其作为SQL中的内联表达式来处理，则最接近的替代方法是用户定义的函数。而且我们知道这不会加快任何速度（除非像在Oracle中那样可以在其上定义索引）。如果必须创建一个函数，则最好在调用SQL的代码中做到这一点声明。

并且该功能可能会在行为上有所不同，具体取决于DBMS和版本。（向所有热衷于能够互换使用任何数据库引擎的Java开发人员大喊大叫。）

领域知识

我们可能对列的域有专门的知识（即，为列强制使用的一组允许值。我们可能会先验地知道，存储在列中的值将永远不会包含百分号，下划线或方括号在这种情况下，我们只需要简要评论一下这些案例就可以了。

列中存储的值可能允许使用％或_字符，但是约束可能要求使用定义的字符对这些值进行转义，以使这些值像是比较“安全”的。同样，快速评论一下允许的值集，尤其是将哪个字符用作转义字符，然后采用Joel Spolsky的方法。

但是，由于缺乏专业知识和保证，对我们来说至少要考虑处理那些不起眼的极端情况，并考虑行为是否合理和“符合规范”，这一点很重要。

总结其他问题

我相信其他人已经充分指出了其他一些普遍认为的关注领域：

SQL注入（采用看似由用户提供的信息，而不是通过绑定变量来提供，而是包含在SQL文本中。不需要使用绑定变量，这只是挫败SQL注入的一种便捷方法。还有其他方法处理方法：
使用索引扫描而不是索引查找的优化程序计划，可能需要使用表达式或函数来转义通配符（可能在表达式或函数上进行索引）
使用文字值代替绑定变量会影响可伸缩性

结论

我喜欢Joel Spolsky的方法。很聪明而且有效。

但是，一看到它，我就立即看到它的潜在问题，让它滑动并不是我的本性。我并不是要批评别人的努力。我知道许多开发人员非常重视自己的工作，因为他们投入了大量资金，并且非常在乎它。因此，请理解，这不是人身攻击。我在这里确定的是在生产而不是测试中出现的问题类型。

是的，我已经离开了最初的问题。但是，关于“我认为是对某个问题的“选定”答案很重要的问题”，还有什么地方需要留下此注释？

更多推荐

参数化SQL IN子句

本文发布于:2024-02-28 06:20:16，感谢您对本站的认可！

本文链接:https://www.elefans.com/category/jswz/34/1768668.html