I think this is a case when a static query is just not the way to go. Dynamically build the list for your in clause, escape your single quotes, and dynamically build SQL. In this case you probably won't see much of a difference with any method due to the small list, but the most efficient method really is to send the SQL exactly as it is written in your post. I think it is a good habit to write it the most efficient way, rather than to do what makes the prettiest code, or consider it bad practice to dynamically build SQL.

I have seen the split functions take longer to execute than the query themselves in many cases where the parameters get large. A stored procedure with table valued parameters in SQL 2008 is the only other option I would consider, although this will probably be slower in your case. TVP will probably only be faster for large lists if you are searching on the primary key of the TVP, because SQL will build a temporary table for the list anyway (if the list is large). You won't know for sure unless you test it.

I have also seen stored procedures that had 500 parameters with default values of null, and having WHERE Column1 IN (@Param1, @Param2, @Param3, ..., @Param500). This caused SQL to build a temp table, do a sort/distinct, and then do a table scan instead of an index seek. That is essentially what you would be doing by parameterizing that query, although on a small enough scale that it won't make a noticeable difference. I highly recommend against having NULL in your IN lists, as if that gets changed to a NOT IN it will not act as intended. You could dynamically build the parameter list, but the only obvious thing that you would gain is that the objects would escape the single quotes for you. That approach is also slightly slower on the application end since the objects have to parse the query to find the parameters. It may or may not be faster on SQL, as parameterized queries call sp_prepare, sp_execute for as many times you execute the query, followed by sp_unprepare.

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

悬崖笔记:

对于您的情况，您所做的任何事情，无论是使用列表中固定数量的项进行参数化(如果不使用则为空)，动态地构建带有或不带有参数的查询，还是使用带有表值参数的存储过程，都不会产生太大的区别。不过，我的一般建议如下:

你的case/简单查询很少参数:

动态SQL，如果测试显示更好的性能，可能会使用参数。

具有可重用执行计划的查询，通过简单地更改参数或如果查询很复杂则调用多次:

带有动态参数的SQL。

带有大列表的查询:

具有表值参数的存储过程。如果列表变化很大，则在存储过程上使用WITH RECOMPILE，或者简单地使用不带参数的动态SQL为每个查询生成新的执行计划。

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

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 

So:

@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

在ColdFusion中，我们只需要:

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

在默认情况下，我将通过向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

除非你有一个长得离谱的字符串，否则这应该与表索引一起工作得很好。

如果需要，你可以把它插入一个临时表，索引它，然后运行一个连接…

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

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#

我听到Jeff/Joel今天在播客上谈论这个(第34集，2008-12-16 (MP3, 31 MB)， 1小时03分38秒-1小时06分45秒)，我想我记得Stack Overflow使用LINQ到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已经足够向后看了，但是Contains子句对我来说似乎太向后看了。当我不得不在工作中为一个项目做类似的查询时，我自然地试图用错误的方式来做这个，在本地数组和SQL Server表之间做一个连接，认为LINQ to SQL翻译器将足够聪明，以某种方式处理翻译。它没有这样做，但它确实提供了一个描述性的错误消息，并指示我使用Contains。

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