Параметризация предложения SQL IN

Вы можете передать параметр в виде строки

Итак, у вас есть строка

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 2008) и сделал бы where exists или внутреннее соединение. Вы также можете использовать XML, используя sp_xml_preparedocument, а затем даже индексировать эту временную таблицу.

Для такого переменного числа аргументов единственный способ, о котором я знаю, - это либо явно сгенерировать SQL, либо сделать что-то, что включает в себя заполнение временной таблицы необходимыми элементами и присоединение к временной таблице.

Это грубо, но если у вас есть хотя бы один, вы можете:

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

Наличие IN ('tag1', 'tag2', 'tag1', 'tag1', 'tag1') будет легко оптимизировано SQL Server. Кроме того, вы получаете прямой поиск по индексу

Вы можете параметризовать значение каждый, например:

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» и индексе массива. Индекс всегда будет целым числом, не создается пользователем и безопасен.

Введенные пользователем значения по-прежнему сохраняются в параметрах, поэтому уязвимости здесь нет.

Редактировать:

Injection concerns aside, take care to note that constructing the command text to accomodate a variable number of parameters (as above) impede's SQL server's ability to take advantage of cached queries. The net result is that you almost certainly lose the value of using parameters in the first place (as opposed to merely inserting the predicate strings into the SQL itself).

Не то, чтобы кешированные планы запросов не ценились, но ИМО этот запрос не настолько сложен, чтобы увидеть большую выгоду от него. Хотя затраты на компиляцию могут приближаться к затратам на выполнение (или даже превышать их), вы все равно говорите о миллисекундах.

Если у вас достаточно оперативной памяти, я бы ожидал, что SQL Server, вероятно, также кэширует план для общего количества параметров. Я полагаю, вы всегда можете добавить пять параметров и позволить неопределенным тегам быть NULL - план запроса должен быть таким же, но мне он кажется довольно уродливым, и я не уверен, что это стоит микрооптимизации (хотя, на Stack Overflow - вполне может быть, оно того стоит).

Кроме того, в SQL Server 7 и более поздних версиях используется запросы с автоматической параметризацией, поэтому использование параметров на самом деле не обязательно с точки зрения производительности - однако это критический с точки зрения безопасности - особенно с такими данными, вводимыми пользователем.

Вот быстрый и грязный прием, который я использовал:

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 "%...%" не индексируются.
• Убедитесь, что у вас нет |, пустых или нулевых тегов, иначе это не сработает.

Есть и другие способы добиться этого, которые некоторые сочтут более чистыми, поэтому, пожалуйста, продолжайте читать.

Для 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 rows
    var firstRecord = values.First();
    var metadata= new SqlMetaData(columnName, SqlDbType.NVarChar, 50); //50 as per SQL Type
    return values.Select(v => 
    {
       var r = new SqlDataRecord(metadata);
       r.SetValues(v);
       return r;
    });
}

Обновлять Согласно @Doug

Пожалуйста, избегайте var metadata = SqlMetaData.InferFromValue(firstRecord, columnName);

Он устанавливает длину первого значения, поэтому, если первое значение составляет 3 символа, его максимальная длина устанавливается равной 3, а другие записи будут усечены, если более 3 символов.

Итак, попробуйте использовать: var metadata= new SqlMetaData(columnName, SqlDbType.NVarChar, maxLen);

Примечание: -1 для максимальной длины.

Возможно, это наполовину неприятный способ сделать это, я использовал его однажды, был довольно эффективным.

В зависимости от ваших целей это может быть полезно.

1. Создайте временная таблица с одним столбцом.
2. INSERT каждое значение поиска в этом столбце.
3. Вместо использования IN вы можете просто использовать свои стандартные правила JOIN. (Гибкость ++)

Это дает немного дополнительной гибкости в том, что вы можете делать, но он больше подходит для ситуаций, когда у вас есть большая таблица для запроса, с хорошей индексацией, и вы хотите использовать параметризованный список более одного раза. Избавляет от необходимости выполнять его дважды и выполнять всю очистку вручную.

Я так и не додумался до профилирования, каким именно был быстрый, но в моей ситуации это было необходимо.

У нас есть функция, которая создает переменную таблицы, к которой вы можете присоединиться:

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

В Холодный синтез мы просто делаем:

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

Я слышал, как Джефф / Джоэл говорил об этом сегодня в подкасте (серия 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 уже достаточно оглядывается назад, но предложение Contains мне кажется лишним. Когда мне приходилось делать аналогичный запрос для проекта на работе, я, естественно, пытался сделать это неправильно, выполняя соединение между локальным массивом и таблицей SQL Server, полагая, что переводчик LINQ to SQL будет достаточно умен, чтобы каким-то образом обработать перевод . Этого не произошло, но появилось сообщение об ошибке, которое было описательным и указывало мне на использование Содержит.

В любом случае, если вы запустите это в настоятельно рекомендуемом LINQPad и запустите этот запрос, вы можете просмотреть фактический SQL, созданный поставщиком SQL LINQ. Он покажет вам, что каждое из значений параметризуется в предложении IN.

Правильный способ, IMHO, - хранить список в символьной строке (ограниченной по длине тем, что поддерживает СУБД); единственная хитрость в том, что (для упрощения обработки) у меня есть разделитель (запятая в моем примере) в начале и в конце строки. Идея состоит в том, чтобы «нормализовать на лету», превратив список в таблицу с одним столбцом, содержащую по одной строке на каждое значение. Это позволяет превратить

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

в

in (select ...)

или (решение, которое я, вероятно, предпочел бы) обычное соединение, если вы просто добавите «отдельный», чтобы избежать проблем с повторяющимися значениями в списке.

К сожалению, методы разрезания строки в значительной степени зависят от продукта. Вот версия 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" должен возвращать столько строк, сколько максимальное количество элементы, которые мы можем найти в списке)

Другое возможное решение - вместо того, чтобы передавать переменное количество аргументов в хранимую процедуру, передать одну строку, содержащую имена, которые вам нужны, но сделать их уникальными, заключив их в '<>'. Затем используйте PATINDEX, чтобы найти имена:

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

Я думаю, что это тот случай, когда статический запрос просто не подходит. Динамически создавайте список для вашего предложения in, избегайте одинарных кавычек и динамически создавайте SQL. В этом случае вы, вероятно, не увидите большой разницы с каким-либо методом из-за небольшого списка, но наиболее эффективный метод действительно - отправить SQL точно так, как он написан в вашем сообщении. Я думаю, что это хорошая привычка писать его наиболее эффективным способом, а не делать то, что делает код самым красивым, или считать плохой практикой динамическое построение SQL.

Я видел, как функции разделения выполняются дольше, чем сам запрос, во многих случаях, когда параметры становятся большими. Хранимая процедура с табличными параметрами в SQL 2008 - единственный другой вариант, который я бы рассмотрел, хотя в вашем случае это, вероятно, будет медленнее. TVP, вероятно, будет быстрее для больших списков, только если вы ищете по первичному ключу TVP, потому что SQL все равно построит временную таблицу для списка (если список большой). Вы не узнаете наверняка, если не протестируете это.

Я также видел хранимые процедуры, которые имели 500 параметров со значениями по умолчанию, равными нулю, и имели WHERE Column1 IN (@ Param1, @ Param2, @ Param3, ..., @ Param500). Это заставило SQL построить временную таблицу, выполнить сортировку / разделение, а затем выполнить сканирование таблицы вместо поиска по индексу. По сути, это то, что вы будете делать, параметризуя этот запрос, хотя и в достаточно малом масштабе, чтобы это не дало заметной разницы. Я настоятельно рекомендую не иметь NULL в ваших списках IN, так как если это будет изменено на NOT IN, оно не будет работать так, как задумано. Вы можете динамически создавать список параметров, но единственное очевидное, что вы получите, - это то, что объекты будут избегать одинарных кавычек за вас. Этот подход также немного медленнее на стороне приложения, поскольку объекты должны анализировать запрос, чтобы найти параметры. Это может быть или не быть быстрее в SQL, поскольку параметризованные запросы вызывают sp_prepare, sp_execute столько раз, сколько вы выполняете запрос, а затем sp_unprepare.

Повторное использование планов выполнения для хранимых процедур или параметризованных запросов может дать вам прирост производительности, но при этом вы будете привязаны к одному плану выполнения, определяемому первым выполняемым запросом. Во многих случаях это может быть не идеальным вариантом для последующих запросов. В вашем случае повторное использование планов выполнения, вероятно, будет плюсом, но это может вообще не иметь никакого значения, поскольку пример представляет собой действительно простой запрос.

Примечания к скалам:

В вашем случае все, что вы делаете, будь то параметризация с фиксированным количеством элементов в списке (null, если не используется), динамическое построение запроса с параметрами или без них или использование хранимых процедур с параметрами, оцениваемыми таблицей, не будет иметь большого значения. . Однако мои общие рекомендации таковы:

Ваш случай / простые запросы с несколькими параметрами:

Динамический SQL, возможно, с параметрами, если тестирование показывает лучшую производительность.

Запросы с многоразовыми планами выполнения, вызываемые несколько раз путем простого изменения параметров или если запрос сложен:

SQL с динамическими параметрами.

Запросы с большими списками:

Хранимая процедура с табличными параметрами. Если список может сильно отличаться, используйте WITH RECOMPILE для хранимой процедуры или просто используйте динамический SQL без параметров для создания нового плана выполнения для каждого запроса.

На мой взгляд, лучший источник для решения этой проблемы - это то, что было размещено на этом сайте:

Комментарии. Динакар Нети

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

Единственный выигрышный ход - не играть.

Для вас нет бесконечного разнообразия. Только конечная изменчивость.

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

Так что в основном, если счетчик равен 0, тогда нет фильтра, и все проходит. Если счетчик больше 0, тогда значение должно быть в списке, но список был дополнен до пяти с невозможными значениями (так что SQL все еще имеет смысл)

Иногда неудачное решение - единственное, что действительно работает.

У меня есть ответ, для которого не требуется UDF, XML Поскольку IN принимает оператор выбора например ВЫБРАТЬ * ИЗ Тест, где ДАННЫЕ ВХОДИТ (ВЫБЕРИТЕ значение ИЗ ТАБЛИЦЫ)

Вам действительно нужен только способ преобразовать строку в таблицу.

Это можно сделать с помощью рекурсивного 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);

Если вы звоните из .NET, вы можете использовать Сеть Dapper dot:

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.Tags
           where names.Contains(tag.Name)
           orderby tag.Count descending
           select tag;

Может быть, здесь можно использовать 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)

Вот метод, который воссоздает локальную таблицу, которая будет использоваться в строке запроса. Это устраняет все проблемы с синтаксическим анализом.

Строку можно построить на любом языке. В этом примере я использовал 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)

Используйте следующую хранимую процедуру. Он использует настраиваемую функцию разделения, которую можно найти здесь.

 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

Вот кросс-пост с решением той же проблемы. Более надежен, чем зарезервированные разделители - включает экранирование и вложенные массивы, а также понимает значения NULL и пустые массивы.

Строка C# и T-SQL [] Вспомогательные функции Pack / Unpack

Затем вы можете присоединиться к функции, возвращающей табличное значение.

Если у вас есть 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

Если внутри предложения 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.

Демо Fiddler

Обновлено: Создайте таблицу под названием 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

Вот еще одна альтернатива. Просто передайте в хранимую процедуру список с разделителями-запятыми в качестве строкового параметра и:

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

Вот еще один ответ на эту проблему.

(новая версия размещена 04.06.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 parameter
                    sqlCommand.Parameters.Add(new SqlParameter("@0", DBNull.Value));
                }
                using (var sqlDataAdapter = new SqlDataAdapter(sqlCommand))
                {
                    sqlDataAdapter.Fill(ds);
                }
            }
        }
        return ds;
    }

Ваше здоровье.

Используйте динамический запрос. Внешний интерфейс предназначен только для создания необходимого формата:

DECLARE @invalue VARCHAR(100)
SELECT @invalue = '''Bishnu'',''Gautam'''

DECLARE @dynamicSQL VARCHAR(MAX)
SELECT @dynamicSQL = 'SELECT * FROM #temp WHERE [name] IN (' + @invalue + ')'
EXEC (@dynamicSQL)

SQL Fiddle

(Обновлено: если параметры с табличным значением недоступны) Кажется, лучше всего разделить большое количество параметров 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 (processing) .Take (size [...]) вместо списка / массива 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 */
}

Я наблюдал за SQL, сгенерированным объектно-реляционным картографом NHibernate (при запросе данных для создания объектов), и он лучше всего выглядит с несколькими запросами. В NHibernate можно указать размер пакета; если нужно получить много строк данных объекта, он пытается получить количество строк, эквивалентное размеру пакета

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

вместо того, чтобы отправлять сотни или тысячи

SELECT * FROM MyTable WHERE 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 нет!) И повторно используют планы запросов, если они доступны. Нет необходимости в отдельных операторах подготовки и утомительном обслуживании дескрипторов запросов в коде! ADO.NET работает так, но похоже, что Java все еще использует подготовку / выполнение по дескриптору (не уверен).

У меня был собственный вопрос по этой теме, изначально предлагавший заполнить предложение IN дубликатами, но затем я предпочел разделение оператора в стиле NHibernate: Параметризованный SQL - входящий / не входящий с фиксированным количеством параметров для оптимизации кеша плана запроса?

Этот вопрос все еще интересен, даже спустя более 5 лет после того, как его задали ...

Обновлено: я заметил, что запросы IN со многими значениями (например, 250 или более) по-прежнему имеют тенденцию быть медленными в данном случае на SQL Server. Хотя я ожидал, что БД создаст своего рода временную таблицу внутри и соединится с ней, казалось, что она повторяет только однозначное выражение SELECT n раз. Время составляло примерно 200 мс на запрос - даже хуже, чем объединение исходных идентификаторов SELECT для извлечения из других связанных таблиц. Кроме того, в SQL Server Profiler было от 10 до 15 единиц ЦП, что необычно для повторного выполнения одного и того же параметризованного запросы, предполагающие, что новые планы запросов были созданы при повторных вызовах. Может быть, специальные запросы, вроде индивидуальных, ничуть не хуже. Для окончательного вывода мне пришлось сравнить эти запросы с неразделенными запросами с изменяющимися размерами, но на данный момент кажется, что длинных предложений IN в любом случае следует избегать.

Я использую более сжатую версию из самых популярных ответов:

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

    create FUNCTION [dbo].[ConvertStringToList]


      (@str VARCHAR (MAX), @delimeter CHAR (1))
        RETURNS 
        @result TABLE (
            [ID] INT NULL)
    AS
    BEG

IN

    DECLARE @x XML 
    SET @x = '<t>' + REPLACE(@str, @delimeter, '</t><t>') + '</t>'

    INSERT INTO @result
    SELECT DISTINCT x.i.value('.', 'int') AS token
    FROM @x.nodes('//t') x(i)
    ORDER BY 1

RETURN
END

--ВАШ ЗАПРОС

select * from table where id in ([dbo].[ConvertStringToList(YOUR comma separated string ,',')])

Я бы подошел к этому по умолчанию, передав функцию с табличным значением (которая возвращает таблицу из строки) в условие 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

Если у вас нет слишком длинной строки, это должно хорошо работать с индексом таблицы.

При необходимости вы можете вставить его во временную таблицу, проиндексировать, а затем запустить соединение ...

В SQL Server 2016+ еще одна возможность - использовать функцию OPENJSON.

Этот подход описан в блоге OPENJSON - один из лучших способов выбора строк по списку идентификаторов.

Полный рабочий пример ниже

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#

В 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. Find rows by list of values

This is replacement for common anti-pattern such as creating a dynamic SQL string in application layer or Transact-SQL, or by using LIKE operator:

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

Дополнение:

Чтобы улучшить оценку строки табличной функции STRING_SPLIT, рекомендуется материализовать разделенные значения как временную таблицу / табличную переменную:

DECLARE @names NVARCHAR(MAX) = 'ruby,rails,scruffy,rubyonrails,sql';

CREATE TABLE #t(val NVARCHAR(120));
INSERT INTO #t(val) SELECT s.[value] FROM STRING_SPLIT(@names, ',') s;

SELECT *
FROM Tags tg
JOIN #t t
  ON t.val = tg.TagName
ORDER BY [Count] DESC;

SEDE - Живая демонстрация

Связанный: Как передать список значений в хранимую процедуру

Это многоразовая вариация решения в отличном ответе Марка Брекета.

Метод расширения:

public static class ParameterExtensions
{
    public static Tuple<string, SqlParameter[]> ToParameterTuple<T>(this IEnumerable<T> values)
    {
        var createName = new Func<int, string>(index => "@value" + index.ToString());
        var paramTuples = values.Select((value, index) => 
        new Tuple<string, SqlParameter>(createName(index), new SqlParameter(createName(index), value))).ToArray();
        var inClause = string.Join(",", paramTuples.Select(t => t.Item1));
        var parameters = paramTuples.Select(t => t.Item2).ToArray();
        return new Tuple<string, SqlParameter[]>(inClause, parameters);
    }
}

Использование:

        string[] tags = {"ruby", "rails", "scruffy", "rubyonrails"};
        var paramTuple = tags.ToParameterTuple();
        var cmdText = $"SELECT * FROM Tags WHERE Name IN ({paramTuple.Item1})";

        using (var cmd = new SqlCommand(cmdText))
        {
            cmd.Parameters.AddRange(paramTuple.Item2);
        }

Есть хороший, простой и проверенный способ сделать это:

/* Create table-value string: */
CREATE TYPE [String_List] AS TABLE ([Your_String_Element] varchar(max) PRIMARY KEY);
GO
/* Create procedure which takes this table as parameter: */

CREATE PROCEDURE [dbo].[usp_ListCheck]
@String_List_In [String_List] READONLY  
AS   
SELECT a.*
FROM [dbo].[Tags] a
JOIN @String_List_In b ON a.[Name] = b.[Your_String_Element];

Я начал использовать этот метод, чтобы исправить проблемы, которые у нас были с фреймворком сущностей (не было достаточно надежным для нашего приложения). Поэтому мы решили дать шанс Dapper (так же, как Stack). Также указание вашего списка строк в виде таблицы со столбцом PK во многом исправляет ваши планы выполнения. Здесь - хорошая статья о том, как передать таблицу в Dapper - все быстро и ЧИСТО.

Создайте временную таблицу, в которой хранятся имена, а затем используйте следующий запрос:

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

В SQL SERVER 2016 или выше вы можете использовать STRING_SPLIT.

DECLARE @InParaSeprated VARCHAR(MAX) = 'ruby,rails,scruffy,rubyonrails'
DECLARE @Delimeter VARCHAR(10) = ','
SELECT 
    * 
FROM 
    Tags T
    INNER JOIN STRING_SPLIT(@InputParameters,@Delimeter) SS ON T.Name = SS.value
ORDER BY 
    Count DESC

Я использую это, потому что иногда соединение выполняется быстрее, чем Оператор Like работает в моих запросах. Кроме того, вы можете поместить неограниченное количество входов в любой разделенный формат, который вам нравится. Мне это нравится ..

Шаг 1:-

string[] Ids = new string[] { "3", "6", "14" };
string IdsSP = string.Format("'|{0}|'", string.Join("|", Ids));

Шаг 2:-

@CurrentShipmentStatusIdArray [nvarchar](255) = NULL

Шаг 3:-

Where @CurrentShipmentStatusIdArray is null or @CurrentShipmentStatusIdArray LIKE '%|' + convert(nvarchar,Shipments.CurrentShipmentStatusId) + '|%'

или же

Where @CurrentShipmentStatusIdArray is null or @CurrentShipmentStatusIdArray LIKE '%|' + Shipments.CurrentShipmentStatusId+ '|%'