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

Это грубо, но если у Вас, как гарантируют, будет по крайней мере один, Вы могли бы сделать:

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

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

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

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

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

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

Я слышал разговор о Jeff/Joel об этом на подкасте сегодня ( эпизод 34 , 2008-12-16 (MP3, 31  МБ), 1:03:38 secs - 1:06:45 secs), и я думал, что вспомнил 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 пункт.

Можно передать параметр как строку

, Таким образом, у Вас есть строка

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 можно использовать табличный параметр . Это - немного работы, но это возможно более чисто, чем мой другой метод .

Первый, необходимо создать тип

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

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

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 не основан на вводе данных пользователем. Это только основано на hardcoded "@tag" префикс и индекс массива. Индекс будет всегда быть целым числом, не быть пользователем, сгенерированным, и безопасен.

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

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

Инжекционные проблемы в стороне, заботьтесь, чтобы отметить что построение текста команды для размещения переменного количества параметров (как выше) способность SQL-сервера impede использовать в своих интересах кэшируемые запросы. Конечный результат состоит в том, что Вы почти наверняка теряете значение использования параметров во-первых (в противоположность простой вставке строк предиката в сам SQL).

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

, Если бы у Вас есть достаточно RAM, я ожидал бы SQL  Сервер, вероятно, кэшировал бы план относительно общих количеств параметров также. Я предполагаю, что Вы могли всегда добавлять пять параметров и позволять неуказанным тегам быть ПУСТЫМИ - план запросов должен быть тем же, но это кажется довольно ужасным мне, и я не уверен, что это было бы стоящий микрооптимизации (хотя, на Stack  Переполнение - это может стоить того).

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

Я передал бы параметр типа таблицы (так как это SQL Server 2008 ), и сделайте where exists, или внутреннее объединение. Можно также использовать XML, с помощью sp_xml_preparedocument, и затем даже индексировать ту временную таблицу.

В ColdFusion мы просто делаем:

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

Надлежащий путь, по моему скромному мнению, состоит в том, чтобы сохранить список в символьной строке (ограниченный в длине какой поддержка DBMS); единственный прием - то, что (для упрощения обработки) у меня есть разделитель (запятая в моем примере) вначале и в конце строки. Идея состоит в том, чтобы "нормализовать на лету", превратив список в таблицу на один столбец, которая содержит одну строку на значение. Это позволяет Вам поворачиваться

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

в

в (выбор...)

или (решение, которое я, вероятно, предпочел бы), регулярное соединение, если Вы просто добавляете "отличное" для предотвращения проблем с дублирующимися значениями в списке.

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

(Конечно, "центр" должен возвратить столько же строк сколько максимальное количество объектов, которые мы можем найти в списке)

Первоначальный вопрос был «Как мне параметризовать запрос ...»

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

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

См. Ответ Марка Брэкетта для предпочтительного ответа, который я (и 231 человек) проголосовали за. Подход, представленный в его ответе, позволяет 1) эффективно использовать связываемые переменные и 2) для предикатов, которые можно саргировать.

Выбранный ответ

Я хочу здесь затронуть подход, данный в ответе Джоэла Спольски, ответ "выбран" как правильный ответ.

Подход Джоэла Спольски умный. И он работает разумно, он будет демонстрировать предсказуемое поведение и предсказуемую производительность, учитывая «нормальные» значения и с нормативными крайними случаями, такими как NULL и пустая строка. И этого может быть достаточно для конкретного приложения.

Но, обобщая этот подход, давайте также рассмотрим более неясные угловые случаи, например, когда столбец Name содержит подстановочный знак (как распознается предикатом LIKE). Наиболее часто используемый подстановочный знак, который я вижу, это % ( знак процента.). Итак, давайте разберемся с этим сейчас, а позже перейдем к другим случаям.

Некоторые проблемы с символом%

Рассмотрим значение Name 'pe% ter' . (В приведенных здесь примерах я использую буквальное строковое значение вместо имени столбца.) Строка со значением имени «pe% ter» будет возвращена запросом формы:

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

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

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

Наблюдаемое нами поведение является довольно странным. Изменение порядка условий поиска в списке изменяет набор результатов. sa ссылка на документацию SQL Server .

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

Теперь мы можем сопоставить литерал%. Конечно, когда у нас есть имя столбца, нам нужно будет динамически избегать подстановочного знака. Мы можем использовать функцию REPLACE , чтобы найти вхождения символа % и вставить символ обратной косой черты перед каждым из них, например:

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

Таким образом, проблема с% подстановочный знак. Почти.

Спасение от побега

Мы понимаем, что наше решение создало еще одну проблему. Эскейп-персонаж. Мы видим, что нам также нужно будет избегать любых вхождений самого escape-символа. На этот раз мы используем! в качестве escape-символа:

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

Подчеркивание тоже

Теперь, когда мы находимся в ударе, мы можем добавить еще один REPLACE обработчик подстановочного символа подчеркивания. И на этот раз для развлечения мы Я буду использовать $ в качестве escape-символа.

select ...
 where '|p_%t!r|'
  like '%|' + REPLACE(REPLACE(REPLACE( 'p_%t!r' ,'$','$$'),'%','$%'),'_','$_') + '|%' escape '$'

Я предпочитаю этот подход к экранированию, потому что он работает в Oracle и MySQL, а также в SQL Server. (Я обычно использую \ обратную косую черту в качестве escape-символа, поскольку именно этот символ мы используем в регулярных выражениях. Но зачем ограничиваться соглашением!

Эти надоедливые скобки

SQL Server также позволяет рассматривать подстановочные знаки как литералы заключив их в квадратные скобки []. Итак, мы еще не закончили исправление, по крайней мере, для SQL Server. Поскольку пары скобок имеют особое значение, нам также нужно будет их избежать. Если нам удастся правильно избежать скобок , то, по крайней мере, нам не придется возиться с дефисом - и каратом ^ в скобках. И мы можем оставить любые % и ] _ символов внутри скобок экранированы, поскольку мы в основном отключили специальное значение скобок.

Поиск подходящих пар скобок не должен быть такой сложной задачей. Это немного сложнее, чем обрабатывать вхождения синглтона% и _. (Обратите внимание, что недостаточно просто исключить все вхождения в скобки, потому что одноэлементная скобка считается буквальной, и ее не нужно экранировать. Логика становится немного более расплывчатой, чем я могу справиться, не выполняя больше тестовых примеров .)

Встроенное выражение становится беспорядочным

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

Функция где?

Хорошо, если мы не обрабатываем это как встроенное выражение в SQL, Ближайшая альтернатива - это функция, определяемая пользователем. И мы знаем, что это никак не ускорит процесс (если мы не можем определить для него индекс, как мы могли бы с Oracle). Если нам нужно создать функцию, нам лучше сделать это в коде, который вызывает SQL

И эта функция может иметь некоторые отличия в поведении в зависимости от СУБД и версии. (Привет всем Java-разработчикам, которые так заинтересованы в возможности взаимозаменяемо использовать любой механизм базы данных.)

Знание предметной области

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

Значения, хранящиеся в столбце, могут допускать символы% или _, но ограничение может потребовать экранирования этих значений, возможно, с использованием определенного символа, таким образом, что значения LIKE сравнение "безопасны". Опять же, быстрый комментарий о разрешенном наборе значений, и в частности о том, какой символ используется в качестве escape-символа, и согласиться с подходом Джоэла Спольски.

Но, при отсутствии специальных знаний и гарантии, для нас важно хотя бы подумайте о том, как справиться с этими неясными угловыми случаями, и подумайте, является ли поведение разумным и «соответствует ли спецификации». Я сразу увидел в этом потенциальную проблему, и не в моем характере позволять этому ускользать. Я не хочу критиковать усилия других. Я знаю, что многие разработчики относятся к своей работе очень лично, потому что они так много вкладывают в нее и очень заботятся о ней. Пожалуйста, поймите, это не личное нападение. Я определяю здесь тип проблемы, который возникает в процессе производства, а не при тестировании.

Да, я далеко ушел от исходного вопроса. Но где еще оставить эту заметку, касающуюся того, что я считаю важным вопросом с «выбранным» ответом на вопрос?

Да, я далеко ушел от исходного вопроса. Но где еще оставить эту заметку, касающуюся того, что я считаю важным вопросом с «выбранным» ответом на вопрос?

Да, я далеко ушел от исходного вопроса. Но где еще оставить эту заметку, касающуюся того, что я считаю важным вопросом с «выбранным» ответом на вопрос?

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

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

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

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

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

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

Примечания от Cliffs:

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

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

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

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

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

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

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

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

Syscomments. Dinakar Nethi

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

CREDITS FOR: Dinakar Nethi