java 参数化sql_sql server - 参数化SQL IN子句

最新推荐文章于 2022-11-22 18:17:27 发布

MicroBlow

最新推荐文章于 2022-11-22 18:17:27 发布

阅读量703

点赞数

文章标签： java 参数化sql

本文链接：https://blog.csdn.net/weixin_33953322/article/details/114219577

版权

sql server - 参数化SQL IN子句

如何参数化包含带有可变数量参数的IN子句的查询，如下所示？

SELECT * FROM Tags

WHERE Name IN ('ruby','rails','scruffy','rubyonrails')

ORDER BY Count DESC

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

我不想为此(或XML)使用专用存储过程，但如果有一些特定于SQL Server 2008的优雅方式，我对此持开放态度。

30个解决方案

684 votes

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

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服务器利用缓存查询的能力。最终结果是你几乎肯定会失去首先使用参数的价值(而不是仅仅将谓词字符串插入SQL本身)。

并不是说缓存的查询计划没有价值，但是IMO这个查询并不是很复杂，足以从中看到很多好处。虽然编译成本可能接近(甚至超过)执行成本，但您仍然在谈论毫秒。

如果你有足够的RAM，我希望SQL Server可能会缓存一个常见的参数计数的计划。我想你总是可以添加五个参数，让未指定的标签为NULL - 查询计划应该是相同的，但对我来说这似乎很难看，我不确定它是否值得进行微优化(尽管如此，在Stack Overflow上 - 它可能非常值得)。

此外，SQL Server 7及更高版本将自动参数化查询，因此从性能角度来看，使用参数并不是必需的 - 然而，从安全角度来看，这是至关重要的 - 特别是对于用户输入的数据。

Mark Brackett answered 2019-01-13T08:20:34Z

292 votes

这是我用过的一种快速而肮脏的技术：

SELECT * FROM Tags

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 "%...%"查询未编入索引。

确保您没有任何|，空白或空标签，否则这将无效

有其他方法可以实现这一点，有些人可能认为更清洁，所以请继续阅读。

Joel Spolsky answered 2019-01-13T08:19:19Z

237 votes

对于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

cmd.Parameters.AddWithValue("@tagNames", tags.AsSqlDataRecord("Name")).SqlDbType = SqlDbType.Structured;

cmd.Parameters["@tagNames"].TypeName = "dbo.TagNamesTableType";

// Extension method for converting IEnumerable to IEnumerable

public static IEnumerable AsSqlDataRecord(this IEnumerable values, string columnName) {

if (values == null || !values.Any()) return null; // Annoying, but SqlClient wants null instead of 0 rows

var firstRecord = values.First();

var metadata = SqlMetaData.InferFromValue(firstRecord, columnName);

return values.Select(v =>

{

var r = new SqlDataRecord(metadata);

r.SetValues(v);

return r;

});

}

Mark Brackett answered 2019-01-13T08:21:10Z

178 votes

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

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

有了这个说法，请继续标记这个答案，将其归类，将其标记为不是答案......做任何你认为正确的事情。

请参阅Mark Brackett的答案，了解我(及其他231人)投票的首选答案。他的回答中给出的方法允许1)有效使用绑定变量，2)用于可搜索的谓词。

选择答案

我想在这里讨论的是Joel Spolsky的回答中给出的方法，答案“选择”作为正确的答案。

Joel Spolsky的方法很聪明。并且它合理地工作，它将展示可预测的行为和可预测的性能，给定“正常”值，以及规范边缘情况，例如NULL和空字符串。并且它对于特定应用可能就足够了。

但是在概括这种方法的方面，我们还要考虑更加模糊的极端情况，例如当[]列包含通配符时(由LIKE谓词识别)。我看到最常用的通配符是-(百分号)。。现在让我们来处理这个问题，然后继续讨论其他案例。

％字符的一些问题

考虑名称值[].(对于此处的示例，我使用文字字符串值代替列名。)名称值为“'pe％ter”的行将由以下格式的查询返回：

select ...

但如果搜索项的顺序颠倒，则不会返回相同的行：

select ...

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

不言而喻，我们可能不希望[]匹配花生酱，无论他多么喜欢它。

朦胧的角落案例

(是的，我会同意这是一个模糊的案例。可能是一个不太可能被测试的案例。我们不希望列值中出现通配符。我们可以假设应用程序阻止存储这样的值。但是根据我的经验，我很少看到数据库约束明确禁止在[]比较运算符右侧被视为通配符的字符或模式。

修补一个洞

修补此漏洞的一种方法是转义[]通配符。 (对于不熟悉运算符的escape子句的人，这里是SQL Server文档的链接。

select ...

where '|peanut|butter|'

like '%|' + 'pe\%ter' + '|%' escape '\'

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

select ...

where '|pe%ter|'

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

这样就解决了％wildcard的问题。几乎。

逃离逃生

我们认识到我们的解决方案引入了另一个问题逃脱角色。我们看到我们还需要逃避任何出现的转义字符本身。这次，我们用了！作为转义字符：

select ...

where '|pe%t!r|'

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

也是下划线

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

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开发人员致敬，他们热衷于能够交替使用任何数据库引擎。)

领域知识

我们可能对列的域具有专门知识(即，为列强制执行的允许值集合。我们可能先验地知道列中存储的值永远不会包含百分号，下划线或括号在这种情况下，我们只是简单地包含这些案例的快速评论。

存储在列中的值可以允许％或_字符，但是约束可能要求对这些值进行转义，可能使用定义的字符，以使值为LIKE比较“安全”。再次，快速评论允许的值集，特别是哪个字符用作转义字符，并与Joel Spolsky的方法一起使用。

但是，如果缺乏专业知识和保证，至少应考虑处理那些模糊不清的角落案件，并考虑行为是否合理并且“符合规范”，这一点非常重要。

其他问题概括

我相信其他人已经充分指出了其他一些常被考虑的关注领域：

SQL注入(看似用户提供的信息，包括SQL文本中的信息，而不是通过绑定变量提供它们。使用绑定变量不是必需的，它只是一种方便的方法来阻止SQL注入。还有其他处理方式：

优化器计划使用索引扫描而不是索引搜索，可能需要表达式或函数来转义通配符(可能的表达式或函数索引)

使用文字值代替绑定变量会影响可伸缩性

结论

我喜欢Joel Spolsky的做法。这很聪明。它有效。

但是当我看到它时，我立刻就看到了它的一个潜在问题，让它滑动不是我的本性。我并不是要批评别人的努力。我知道很多开发人员都非常个性化，因为他们投入了大量资金，而且他们非常关心它。所以请理解，这不是个人攻击。我在这里发现的是在生产而不是测试中出现的问题类型。

是的，我离原始问题远远不够。但是还有什么地方可以留下这个关于我认为是一个问题的“选定”答案的重要问题的说明？

spencer7593 answered 2019-01-13T08:26:55Z

125 votes

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

所以你有字符串

DECLARE @tags

SET @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) <> 0

BEGIN

SELECT

@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) > 0

INSERT INTO @List SELECT @sItem

END

IF LEN(@sInputList) > 0

INSERT INTO @List SELECT @sInputList -- Put the last item in

RETURN

END

David Basarab answered 2019-01-13T08:27:50Z

64 votes

我听说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 Tags

where inValues.Contains(tag.Name)

select tag;

而已。并且，是的，LINQ已经足够向后看，但IN条款对我来说似乎更加倒退。当我不得不对工作中的项目进行类似的查询时，我自然会尝试通过在本地数组和SQL Server表之间进行连接来做错误的方法，认为LINQ to SQL转换器足够智能来处理翻译不知何故。它没有，但确实提供了一个描述性的错误信息，并指出我使用Contains。

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

Peter Meyer answered 2019-01-13T08:28:41Z

46 votes

如果从.NET调用，可以使用Dapper dot net：

string[] names = new string[] {"ruby","rails","scruffy","rubyonrails"};

var tags = dataContext.Query(@"

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.Tags

where names.Contains(tag.Name)

orderby tag.Count descending

select tag;

Marc Gravell answered 2019-01-13T08:29:18Z

25 votes

这可能是一种令人讨厌的方式，我用了一次，相当有效。

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

使用一列创建临时表。

IN每个查找该列的值。

您可以使用标准JOIN规则，而不是使用IN。 (灵活性++)

这有一些额外的灵活性，你可以做什么，但它更适合你有一个大表要查询，具有良好的索引，并且你想不止一次使用参数化列表的情况。节省必须执行两次并手动完成所有卫生。

我从来没有准确地分析它到底有多快，但在我的情况下它是需要的。

Kent Fredric answered 2019-01-13T08:30:29Z

21 votes

我们有一个函数可以创建一个可以加入的表变量：

ALTER FUNCTION [dbo].[Fn_sqllist_to_table](@list AS VARCHAR(8000),

@delim AS VARCHAR(10))

RETURNS @listTable TABLE(

Position INT,

Value VARCHAR(8000))

BEGIN

DECLARE @myPos INT

SET @myPos = 1

WHILE Charindex(@delim, @list) > 0

BEGIN

INSERT INTO @listTable

(Position,Value)

VALUES (@myPos,LEFT(@list, Charindex(@delim, @list) - 1))

SET @myPos = @myPos + 1

IF Charindex(@delim, @list) = Len(@list)

INSERT INTO @listTable

(Position,Value)

VALUES (@myPos,'')

SET @list = RIGHT(@list, Len(@list) - Charindex(@delim, @list))

END

IF Len(@list) > 0

INSERT INTO @listTable

(Position,Value)

VALUES (@myPos,@list)

RETURN

END

所以：

@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

David Robbins answered 2019-01-13T08:30:53Z

16 votes

这很糟糕，但如果你保证至少有一个，你可以这样做：

SELECT ...

...

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

IN('tag1'，'tag2'，'tag1'，'tag1'，'tag1')将很容易被SQL Server优化。另外，您可以获得直接索引搜索

Matt Rogish answered 2019-01-13T08:31:22Z

15 votes

在我看来，解决这个问题的最佳来源是在这个网站上发布的内容：

SYSCOMMENTS。 Dinakar Nethi

CREATE FUNCTION dbo.fnParseArray (@Array VARCHAR(1000),@separator CHAR(1))

RETURNS @T Table (col1 varchar(50))

BEGIN

--DECLARE @T Table (col1 varchar(50))

-- @Array is the array we wish to parse

-- @Separator is the separator charactor such as a comma

DECLARE @separator_position INT -- This is used to locate each separator character

DECLARE @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 value

SET @array = @array + @separator

-- Loop through the string searching for separtor characters

WHILE PATINDEX('%' + @separator + '%', @array) <> 0

BEGIN

-- patindex matches the a pattern against a string

SELECT @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 string

SELECT @array = STUFF(@array, 1, @separator_position, '')

END

RETURN

END

使用：

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

奖励：Dinakar Nethi

Paulo Henrique answered 2019-01-13T08:32:00Z

15 votes

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

eulerfx answered 2019-01-13T08:32:23Z

12 votes

在SQL Server 2008你可以使用SQL Server 2008功能：

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;

SQL Server 2008

接受的答案当然是有效的，这是其中一种方法，但它是反模式的。

E.按值列表查找行

这是常见反模式的替代，例如在应用程序层或Transact-SQL中创建动态SQL字符串，或者使用LIKE运算符：

SQL Server 2008

原始问题有要求SQL Server 2008.因为这个问题经常被用作副本，所以我添加了这个答案作为参考。

Lukasz Szozda answered 2019-01-13T08:33:22Z

9 votes

恕我直言的正确方法是将列表存储在字符串中(由DBMS支持的长度限制); 唯一的技巧是(为了简化处理)我在字符串的开头和结尾有一个分隔符(在我的例子中是一个逗号)。我们的想法是“动态规范化”，将列表转换为单列表，每个值包含一行。这可以让你转身

in(ct1，ct2，ct3 ... ctn)

变成一个

在(选择...)

或者(我可能更喜欢的解决方案)常规连接，如果你只是添加一个“distinct”来避免列表中重复值的问题。

不幸的是，切片字符串的技术是特定于产品的。这是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 names

from (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' names) as list

union all

select (n - 1) as n,

substring(names, 1 + charindex(',', names), len(names)) as names

from qry

where n > 1)

select n, substring(names, 1, charindex(',', names) - 1) dwarf

from qry;

Oracle版本：

select n, substr(name, 1, instr(name, ',') - 1) dwarf

from (select n,

substr(val, 1 + instr(val, ',', 1, n)) name

from (select rownum as n,

list.val

from (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' val

from dual) list

connect 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 n

union all

select 2 as n

union all

select 3 as n

union all

select 4 as n

union all

select 5 as n

union all

select 6 as n

union all

select 7 as n

union all

select 8 as n

union all

select 9 as n

union all

select 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, ',', ''));

(当然，“pivot”必须返回与最大数量一样多的行我们可以在列表中找到的项目)

Jeff Atwood answered 2019-01-13T08:34:38Z

9 votes

如果您有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

Jodrell answered 2019-01-13T08:35:13Z

8 votes

默认情况下，我会将表值函数(从字符串返回一个表)传递给IN条件。

这是UDF的代码(我从Stack Overflow的某个地方得到它，我现在找不到源代码)

CREATE FUNCTION [dbo].[Split] (@sep char(1), @s varchar(8000))

RETURNS table

RETURN (

WITH Pieces(pn, start, stop) AS (

SELECT 1, 1, CHARINDEX(@sep, @s)

UNION ALL

SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)

FROM Pieces

WHERE stop > 0

)

SELECT

SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s

FROM Pieces

)

一旦你得到了这个，你的代码就像这样简单：

select * from Tags

where Name in (select s from dbo.split(';','ruby;rails;scruffy;rubyonrails'))

order by Count desc

除非你有一个可笑的长字符串，否则这应该适用于表索引。

如果需要，您可以将其插入临时表，索引它，然后运行连接...

Eli Ekstein answered 2019-01-13T08:36:05Z

7 votes

我认为这是静态查询不可行的情况。动态构建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为每个查询生成新的执行计划。

Scott answered 2019-01-13T08:37:50Z

7 votes

可能我们可以在这里使用XML：

declare @x xml

set @x='

With CTE AS (

SELECT

x.item.value('@myvalue[1]', 'decimal') AS myvalue

FROM @x.nodes('//items/item') AS x(item) )

select * from YourTable where tableColumnName in (select myvalue from cte)

MindLoggedOut answered 2019-01-13T08:38:12Z

7 votes

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

create stored procedure GetSearchMachingTagNames

@PipeDelimitedTagNames varchar(max),

@delimiter char(1)

begin

select * from Tags

where Name in (select data from [dbo].[Split](@PipeDelimitedTagNames,@delimiter)

end

mangeshkt answered 2019-01-13T08:38:35Z

6 votes

另一种可能的解决方案是，不是将可变数量的参数传递给存储过程，而是传递包含您所追求的名称的单个字符串，但通过用'＆lt;＆gt;'包围它们来使它们成为唯一的。然后使用PATINDEX查找名称：

SELECT *

FROM Tags

WHERE PATINDEX('%%',',,,') > 0

ArtOfCoding answered 2019-01-13T08:38:58Z

6 votes

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

该字符串可以使用任何语言构建。在这个例子中我使用了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)

Rockfish answered 2019-01-13T08:39:46Z

6 votes

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

CREATE PROCEDURE [dbo].[sp_myproc]

@UnitList varchar(MAX) = '1,2,3'

select column from table

where ph.UnitID in (select * from CsvToInt(@UnitList))

功能：

CREATE Function [dbo].[CsvToInt] ( @Array varchar(MAX))

returns @IntTable table

(IntValue int)

begin

declare @separator char(1)

set @separator = ','

declare @separator_position int

declare @array_value varchar(MAX)

set @array = @array + ','

while patindex('%,%' , @array) <> 0

begin

select @separator_position = patindex('%,%' , @array)

select @array_value = left(@array, @separator_position - 1)

Insert @IntTable

Values (Cast(@array_value as int))

select @array = stuff(@array, 1, @separator_position, '')

end

return

end

Metaphor answered 2019-01-13T08:40:16Z

6 votes

如果我们在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 st

SELECT * FROM Tags

WHERE CharIndex(Name,@Param)>0

Gowdhaman008 answered 2019-01-13T08:41:17Z

5 votes

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

ConcernedOfTunbridgeWells answered 2019-01-13T08:41:40Z

5 votes

在ColdFusion中我们只做：

select * from sometable where values in

rip747 answered 2019-01-13T08:42:02Z

5 votes

我使用了一个更简洁的最高投票答案版本：

List 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();

var paramNames = new List();

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);

George Stocker answered 2019-01-13T08:42:42Z

5 votes

在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)

CREATE PROC dbo.SomeProc

@Tags VARCHAR(MAX)

SELECT T.*

FROM dbo.Tags T

WHERE T.Name IN (SELECT J.Value COLLATE Latin1_General_CI_AS

FROM OPENJSON(CONCAT('[', @Tags, ']')) J)

ORDER BY T.Count DESC

EXEC dbo.SomeProc @Tags = '"ruby","rails","scruffy","rubyonrails"'

DROP TABLE dbo.Tags

Martin Smith answered 2019-01-13T08:43:18Z

4 votes

我有一个不需要UDF，XML的答案因为IN接受select语句例如 SELECT * FROM测试数据IN(SELECT值FROM TABLE)

您真的只需要一种方法将字符串转换为表格。

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

这是CTE版本。

DECLARE @InputString varchar(8000) = 'ruby,rails,scruffy,rubyonrails'

SELECT @InputString = @InputString + ','

;WITH RecursiveCSV(x,y)

(

SELECT

x = SUBSTRING(@InputString,0,CHARINDEX(',',@InputString,0)),

y = SUBSTRING(@InputString,CHARINDEX(',',@InputString,0)+1,LEN(@InputString))

UNION ALL

SELECT

x = SUBSTRING(y,0,CHARINDEX(',',y,0)),

y = SUBSTRING(y,CHARINDEX(',',y,0)+1,LEN(y))

FROM

RecursiveCSV

WHERE

SUBSTRING(y,CHARINDEX(',',y,0)+1,LEN(y)) <> '' OR

SUBSTRING(y,0,CHARINDEX(',',y,0)) <> ''

)

SELECT

FROM