提问



如何参数化包含具有可变数量参数的IN子句的查询,如下所示?


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


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


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

最佳参考


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


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

  • 确保您没有任何|,空白或空标签,或者这不会起作用



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

其它参考1


您可以参数化每个值,例如:


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前缀和数组的索引。索引总是是一个整数,不是用户生成的,而且是安全的。[80]


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


编辑:



  除了注入问题之外,请注意构建命令文本以容纳可变数量的参数(如上所述)会妨碍SQL服务器利用缓存查询的能力。最终结果是你几乎肯定会失去首先使用参数的价值(而不是仅仅将谓词字符串插入SQL本身)。



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


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


此外,SQL  Server 7及更高版本将自动参数化查询,因此从性能角度来看,使用参数并不是必需的 - 但从安全角度来看,它是 critical - 特别是对于用户输入的数据像这样。[81]

其它参考2


对于SQL Server 2008,您可以使用表值参数。这有点工作,但它可以说比我的其他方法更清晰。[82]


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


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

其它参考3


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


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


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


请参阅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比较运算符右侧被视为通配符的字符或模式。


修补一个洞


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


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


现在我们可以匹配文字%。当然,当我们有一个列名时,我们需要动态转义通配符。我们可以使用REPLACE函数来查找%字符的出现并在前面插入一个反斜杠字符每一个,像这样:


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


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


逃脱逃脱


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


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


领域知识


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


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


但是,如果缺乏专业知识和保证,至少考虑处理那些模糊不清的角落案件,并考虑行为是否合理并且符合规范对我们来说非常重要。





概述的其他问题


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



  • SQL注入(看似用户提供的信息,包括在SQL文本中的信息,而不是通过绑定变量提供它们。使用绑定变量不是必需的,它只是一种方便的方法来阻止SQL注射。还有其他方法可以处理它:[85]

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

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






结论


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


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


是的,我已经远离了原来的问题。但是还有什么地方可以留下这个关于我认为是一个问题的选定答案的重要问题的说明?

其它参考4


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


所以你有字符串


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

其它参考5


我听说Jeff/Joel今天在播客上谈论这个(第34集,2008-12-16(MP3,31  MB),1小时03分38秒 - 1小时06分45秒) ,我以为我记得Stack  Overflow正在使用LINQ 到SQL,但也许它已被抛弃了。在LINQ 到SQL中也是如此。[86] [87]


var inValues = new [] { "ruby","rails","scruffy","rubyonrails" };

var results = from tag in Tags
              where inValues.Contains(tag.Name)
              select tag;


就是这样。而且,是的,LINQ已经足够向后看了,但是Contains条款似乎对我来说是额外的倒退。当我不得不对工作中的项目进行类似的查询时,我自然会尝试这样做通过在本地数组和SQL Server表之间进行连接的错误方法,将LINQ转换为SQL转换器将足够智能以某种方式处理转换。它没有,但它确实提供了描述性的错误消息指向我使用包含。


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

其它参考6


如果您使用.NET调用,可以使用Dapper dot net:[89]


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 到SQL可能有类似的东西:[90]


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;

其它参考7


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


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



  1. 使用一列创建临时表。

  2. INSERT每个查找值到该列。

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



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


我从来没有准确地分析它是如何快速,但在我的情况下它是需要的。

其它参考8


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


ALTER FUNCTION [dbo].[Fn_sqllist_to_table](@list  AS VARCHAR(8000),
                                           @delim AS VARCHAR(10))
RETURNS @listTable TABLE(
  Position INT,
  Value    VARCHAR(8000))
AS
  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

其它参考9


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


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


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

其它参考10


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


SYSCOMMENTS。 Dinakar Nethi [91]


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

其它参考11


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

其它参考12


SQL Server 2016+中你可以使用SPLIT_STRING功能:[93]


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 [94]


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



  电子。按值列表查找行

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


SELECT ProductId, Name, Tags
FROM Product
WHERE ',1,2,3,' LIKE '%,' + CAST(ProductId AS VARCHAR(20)) + ',%';





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

其它参考13


恕我直言的正确方法是将列表存储在字符串中(由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必须返回与最大数量一样多的行
我们可以在列表中找到的项目)

其它参考14


如果你有SQL Server 2008或更高版本我将使用表值参数。[96]


如果你太不幸被卡在SQL Server 2005上,你可以添加像这样的CLR函数,[98] [99]


[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

其它参考15


我默认情况下将表值函数(从字符串返回一个表)传递给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 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


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


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

其它参考16


我认为这是静态查询不可行的情况。动态构建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。


对存储过程或参数化查询执行计划的重用可能会为您带来性能提升,但它会将您锁定到由执行的第一个查询确定的一个执行计划。在许多情况下,这可能不太适合后续查询。在您的情况下,重用执行计划可能是一个加号,但它可能没有任何区别,因为该示例是一个非常简单的查询。


Cliffs说明:


对于您所做的任何事情,无论是在列表中使用固定数量的项进行参数化(如果不使用,则为null),使用或不使用参数动态构建查询,或者使用具有表值参数的存储过程不会产生太大差异。但是,我的一般建议如下:


您的案例/简单查询,参数很少:


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


使用可重复使用的执行计划进行查询,只需更改参数或查询很复杂即可多次调用:


带动态参数的SQL。


包含大型列表的查询


带有表值参数的存储过程。如果列表可能大量变化,请在存储过程上使用WITH RECOMPILE,或者只使用不带参数的动态SQL为每个查询生成新的执行计划。

其它参考17


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


    declare @x xml
    set @x='<items>
    <item myvalue="29790" />
    <item myvalue="31250" />
    </items>
    ';
    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)

其它参考18


使用以下存储过程。它使用自定义分割功能,可在此处找到。[100]


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

其它参考19


另一种可能的解决方案是传递可变数量的参数对于一个存储过程,传递一个包含你所追求的名字的字符串,但是通过用<>包围它们使它们成为唯一的。然后使用PATINDEX查找名称:


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

其它参考20


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


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

其它参考21


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


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

其它参考22


如果我们在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映射该参数。


小提琴演示[101]


修改
使用以下脚本创建名为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

其它参考23


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

其它参考24


在ColdFusion中我们只做:[102]


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

其它参考25


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


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

其它参考26


在SQL Server 2016+中,另一种可能性是使用OPENJSON函数。[104]


这种方法是在OPENJSON中发布的博客 - 这是通过ID列表选择行的最佳方法之一。[105]


下面是一个完整的例子


CREATE TABLE dbo.Tags
  (
     Name  VARCHAR(50),
     Count INT
  )

INSERT INTO dbo.Tags
VALUES      ('VB',982), ('ruby',1306), ('rails',1478), ('scruffy',1), ('C#',1784)

GO

CREATE PROC dbo.SomeProc
@Tags VARCHAR(MAX)
AS
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

GO

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

DROP TABLE dbo.Tags 

其它参考27


我有一个不需要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) 
AS 
(
    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 
    Tags
WHERE 
    Name IN (select x FROM RecursiveCSV)
OPTION (MAXRECURSION 32767);

其它参考28


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


(新版本发布于2013年6月4日)。


    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 parameter
                    sqlCommand.Parameters.Add(new SqlParameter("@0", DBNull.Value));
                }
                using (var sqlDataAdapter = new SqlDataAdapter(sqlCommand))
                {
                    sqlDataAdapter.Fill(ds);
                }
            }
        }
        return ds;
    }


干杯。

其它参考29


这是对同一问题的解决方案的交叉帖子。比保留分隔符更强大 - 包括转义和嵌套数组,并理解NULL和空数组。


C#& T-SQL string [[]]打包/解包实用程序函数


然后,您可以加入表值函数。