唯一的制胜方法就是不玩。

对你来说没有无限的可变性。只有有限的可变性。

在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 value
  return ExecuteQuery<PublishDate>(getValuesInListSQL, 
               origCount,   
               idList[0], idList[1], idList[2], idList[3], idList[4]);

基本上，如果count为0，那么就没有过滤器，所有东西都会通过。如果计数高于0，则该值必须在列表中，但列表已被填充为5个不可能的值(因此SQL仍然有意义)

有时蹩脚的解决方案是唯一真正有效的。

创建一个存储名称的临时表，然后使用以下查询:

select * from Tags 
where Name in (select distinct name from temp)
order by Count desc

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

这是弦

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

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

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 

您可以通过以下方法以可重用的方式完成此任务-

public static class SqlWhereInParamBuilder
{
    public static string BuildWhereInClause<t>(string partialClause, string paramPrefix, IEnumerable<t> parameters)
    {
        string[] parameterNames = parameters.Select(
            (paramText, paramNumber) => "@" + paramPrefix + paramNumber.ToString())
            .ToArray();

        string inClause = string.Join(",", parameterNames);
        string whereInClause = string.Format(partialClause.Trim(), inClause);

        return whereInClause;
    }

    public static void AddParamsToCommand<t>(this SqlCommand cmd, string paramPrefix, IEnumerable<t> parameters)
    {
        string[] parameterValues = parameters.Select((paramText) => paramText.ToString()).ToArray();

        string[] parameterNames = parameterValues.Select(
            (paramText, paramNumber) => "@" + paramPrefix + paramNumber.ToString()
            ).ToArray();

        for (int i = 0; i < parameterNames.Length; i++)
        {
            cmd.Parameters.AddWithValue(parameterNames[i], parameterValues[i]);
        }
    }
}

要了解更多细节，请参阅这篇博客文章-参数化SQL WHERE IN子句c#

(编辑:如果表值参数不可用) 最好的方法似乎是将大量的IN参数分割为多个固定长度的查询，这样您就有了许多具有固定参数计数的已知SQL语句，并且没有虚值/重复值，也没有对字符串、XML等进行解析。

下面是我用c#写的一些关于这个主题的代码:

public static T[][] SplitSqlValues<T>(IEnumerable<T> values)
{
    var sizes = new int[] { 1000, 500, 250, 125, 63, 32, 16, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1 };
    int processed = 0;
    int currSizeIdx = sizes.Length - 1; /* start with last (smallest) */
    var splitLists = new List<T[]>();

    var valuesDistSort = values.Distinct().ToList(); /* remove redundant */
    valuesDistSort.Sort();
    int totalValues = valuesDistSort.Count;

    while (totalValues > sizes[currSizeIdx] && currSizeIdx > 0)
    currSizeIdx--; /* bigger size, by array pos. */

    while (processed < totalValues)
    {
        while (totalValues - processed < sizes[currSizeIdx]) 
            currSizeIdx++; /* smaller size, by array pos. */
        var partList = new T[sizes[currSizeIdx]];
        valuesDistSort.CopyTo(processed, partList, 0, sizes[currSizeIdx]);
        splitLists.Add(partList);
        processed += sizes[currSizeIdx];
    }
    return splitLists.ToArray();
}

(你可能有进一步的想法，省略排序，使用valuesDistSort.Skip(processed). take (size[…])而不是list/array CopyTo)。

当插入参数变量时，您可以创建如下内容:

foreach(int[] partList in splitLists)
{
    /* here: question mark for param variable, use named/numbered params if required */
    string sql = "select * from Items where Id in("
        + string.Join(",", partList.Select(p => "?")) 
        + ")"; /* comma separated ?, one for each partList entry */

    /* create command with sql string, set parameters, execute, merge results */
}

我观察过NHibernate对象关系映射器生成的SQL(当查询数据并从中创建对象时)，它在多个查询下看起来最好。在NHibernate中，可以指定批处理大小;如果需要获取许多对象数据行，它将尝试检索与批处理大小相等的行数

SELECT * FROM MyTable WHERE Id IN (@p1, @p2, @p3, ... , @p[batch-size])

，而不是发送数百或数千

SELECT * FROM MyTable WHERE Id=@id

当剩余的id小于批处理大小，但仍然大于一个时，它会分割成更小的语句，但仍然具有一定的长度。

如果批处理大小为100，查询有118个参数，它将创建3个查询:

一个有100个参数(批量大小)， 然后是12个 另一个是6，

但没有一个是118或18。通过这种方式，它将可能的SQL语句限制为可能的已知语句，防止太多不同的查询计划，从而填充缓存，并且大部分永远不会被重用。上面的代码做了同样的事情，但是长度为1000、500、250、125、63、32、16、10到1。超过1000个元素的参数列表也会被分割，以防止由于大小限制而导致的数据库错误。

无论如何，最好有一个直接发送参数化SQL的数据库接口，而不需要单独的Prepare语句和句柄来调用。像SQL Server和Oracle这样的数据库通过字符串相等来记住SQL(值会改变，绑定SQL中的参数不会!)并重用查询计划(如果可用的话)。不需要单独的prepare语句，也不需要在代码中维护查询句柄!ADO。NET是这样工作的，但是Java似乎仍然使用prepare/execute by句柄(不确定)。

关于这个主题，我有自己的问题，最初建议用重复的IN子句填充，但后来更喜欢NHibernate样式语句split: 参数化SQL -在/不在与固定数量的参数，查询计划缓存优化?

这个问题仍然很有趣，即使在被问了5年多之后……

EDIT: I noted that IN queries with many values (like 250 or more) still tend to be slow, in the given case, on SQL Server. While I expected the DB to create a kind of temporary table internally and join against it, it seemed like it only repeated the single value SELECT expression n-times. Time was up to about 200ms per query - even worse than joining the original IDs retrieval SELECT against the other, related tables.. Also, there were some 10 to 15 CPU units in SQL Server Profiler, something unusual for repeated execution of the same parameterized queries, suggesting that new query plans were created on repeated calls. Maybe ad-hoc like individual queries are not worse at all. I had to compare these queries to non-split queries with changing sizes for a final conclusion, but for now, it seems like long IN clauses should be avoided anyway.