online - Параметрирование предложения SQL IN




w3 sql sandbox (20)

В SQL Server 2016+ вы можете использовать функцию SPLIT_STRING :

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. Найдите строки по списку значений

Это замена общего анти-шаблона, например создание динамической строки SQL в прикладном уровне или Transact-SQL или с помощью оператора LIKE:

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

У исходного вопроса есть требование SQL Server 2008 . Поскольку этот вопрос часто используется как дубликат, я добавил этот ответ в качестве ссылки.

Как я могу параметризовать запрос, содержащий предложение IN , с переменным числом аргументов, как этот?

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

В этом запросе количество аргументов может быть от 1 до 5.

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


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

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

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


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

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

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

Если у вас есть SQL Server 2008 или более поздняя версия, я бы использовал параметр Table Valued Parameter .

Если вам не повезло, что вы застряли на 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

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

Syscomments. Динакар Нети

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

КРЕДИТЫ ДЛЯ: Динакар Нети


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

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

в

in (выберите ...)

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

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


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

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

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

В зависимости от ваших целей это может пригодиться.

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

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

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


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


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


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


У меня есть ответ, который не требует UDF, XML. Поскольку IN принимает инструкцию select, например SELECT * FROM Test, где Data IN (SELECT Value 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);

В 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 

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

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

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

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

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

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

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

 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

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

Я видел, что функции split выполняют больше времени, чем сам запрос, во многих случаях, когда параметры становятся большими. Хранимая процедура с параметрами таблицы в 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 без параметров для создания нового плана выполнения для каждого запроса.





parameters