Parametryzacja klauzuli SQL

Możesz parametryzować każdą wartość , więc coś w stylu:

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

Który da ci:

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"

Nie, to nie jest otwarte dla SQL injection. Jedyny wtryskiwany tekst do tekstu polecenia nie jest oparty na danych wejściowych użytkownika. Opiera się wyłącznie na zakodowanym na twardo prefiksie "@tag " i indeksie tablicy. Indeks Zawsze będzie liczbą całkowitą, nie jest generowany przez użytkownika i jest bezpieczny.

Wprowadzone przez użytkownika wartości są nadal wprowadzane do parametrów, więc nie ma wrażliwość.

Edit:

Injection dotyczy na bok, zwróć uwagę, że konstruowanie tekstu polecenia, aby pomieścić zmienną liczbę parametrów (jak powyżej) utrudnia SQL server zdolność do korzystania z zapytań buforowanych. Wynikiem netto jest to, że prawie na pewno tracisz wartość używania parametrów w pierwszej kolejności(w przeciwieństwie do zwykłego wstawiania ciągów predykatów do samego SQL).

Nie to, że buforowane plany zapytań nie są wartościowe, ale IMO to zapytanie nie jest wystarczająco skomplikowane, aby zobaczyć wiele korzyści z niego. Chociaż koszty kompilacji mogą zbliżać się (lub nawet przekraczać) do kosztów wykonania, nadal mówimy o milisekundach.

Jeśli masz wystarczająco dużo pamięci RAM, spodziewałbym się, że SQL Server prawdopodobnie będzie buforować plan dla wspólnych liczników parametrów, jak również. Przypuszczam, że zawsze możesz dodać pięć parametrów i pozwolić nieokreślonym tagom być NULL - Plan zapytań powinien być taki sam, ale wydaje mi się to dość brzydkie i nie jestem pewien że warto mikro-optymalizacji (choć na Stack Overflow-to może być warto).

Ponadto, SQL Server 7 i późniejsze będą automatycznie parametryzować zapytania , więc używanie parametrów nie jest tak naprawdę konieczne z punktu widzenia wydajności - jest to jednak krytyczne z punktu widzenia bezpieczeństwa - szczególnie przy wprowadzanych przez Użytkownika Danych, takich jak ten.

Dla SQL Server 2008, można użyć parametru table valued . To trochę pracy, ale jest prawdopodobnie czystsze niż moja inna metoda .

Najpierw musisz utworzyć typ

CREATE TYPE dbo.TagNamesTableType AS TABLE ( Name nvarchar(50) )

Then, your ADO.NET kod wygląda tak:

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

Pierwotne pytanie brzmiało " Jak parametryzować zapytanie ..."

Pozwolę sobie tutaj stwierdzić, że to jest a nie ODPOWIEDŹ na pierwotne pytanie. Istnieją już pewne demonstracje tego w innych dobrych odpowiedziach.

Mając to powiedziane, śmiało Oznacz tę odpowiedź, odrzuć ją, oznacz ją jako nie ODPOWIEDŹ... rób, co uważasz za słuszne.

Zobacz odpowiedź Marka Bracketta dla preferowanej odpowiedzi, którą ja (i 231 innych) podałem. Na podejście podane w jego odpowiedzi pozwala 1) na efektywne wykorzystanie zmiennych bind oraz 2) na predykaty, które są sargable.

Wybrana odpowiedź

Chcę tutaj odnieść się do podejścia podanego w odpowiedzi Joela Spolsky ' ego, odpowiedź "wybrana" jako właściwa odpowiedź.

Ale w kategoriach uogólniających to podejście, rozważmy również bardziej niejasne przypadki narożne, jak gdy kolumna Name zawiera znak wieloznaczny (rozpoznawany przez podobny predykat.) Najczęściej używanym znakiem wieloznacznym jest % (znak procentowy.). Zajmijmy się tym teraz, a później przejdźmy do innych spraw.

Niektóre problemy z % znak

Rozważmy wartość nazwy 'pe%ter'. (W przykładach tutaj używam dosłownej wartości łańcuchowej zamiast nazwy kolumny.) Wiersz o nazwie "pe%ter" zostanie zwrócony przez zapytanie w postaci:

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

Ale ten sam wiersz zostanie zwrócony , a nie, jeśli kolejność wyszukiwanych haseł zostanie odwrócona:

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

It prawie nie trzeba mówić, że możemy nie chcieć dopasować masła orzechowego, bez względu na to, jak bardzo go lubi.

Obudowa narożna

Łatanie dziury

Jednym z sposobów łatania tej dziury jest ucieczka % znaków wieloznacznych. (Dla każdego, kto nie zna klauzuli escape na operatorze, oto link do dokumentacji SQL Server.

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

Teraz możemy dopasować dosłowne %. Oczywiście, gdy mamy nazwę kolumny, będziemy musieli dynamicznie uciec od wildcard. Możemy użyć funkcji REPLACE, aby znaleźć wystąpienia znaku %i wstawić znak odwrotnego ukośnika przed każdym z nich, jak to:

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

Więc to rozwiązuje problem z % wildcard. Prawie.

Escape The escape

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

Podkreślenie too

Teraz, gdy jesteśmy na fali, możemy dodać kolejny REPLACE uchwycić podkreślenie wildcard. I tak dla Zabawy, tym razem użyjemy $ jako escape character.

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

Wolę takie podejście od ucieczki, ponieważ działa w Oracle i MySQL oraz SQL Server. (Zwykle używam \ backslash jako znaku escape, ponieważ jest to znak, którego używamy w wyrażeniach regularnych. Ale po co być ograniczonym przez zjazd!

Te brzydkie nawiasy

Znalezienie pasujących par nawiasów nie powinno być takie trudne. Jest to trochę trudniejsze niż radzenie sobie z wystąpieniami singleton % i _. (Zauważ, że nie wystarczy po prostu wymykać się wszystkim wystąpieniom nawiasów, ponieważ nawias singletonowy jest uważany za literalny i nie musi być unikany. Logika jest coraz bardziej fuzzier niż ja obsługa bez uruchamiania większej liczby przypadków testowych.)

Inline expression gets messy

To inline wyrażenie w SQL jest coraz dłuższe i brzydsze. Prawdopodobnie uda nam się, ale niech Bóg ma w opiece biedną duszę, która przychodzi i musi ją rozszyfrować. Jako fan jestem za wyrażeniami inline, jestem skłonny nie używać tutaj, głównie dlatego, że nie chcę zostawiać komentarza wyjaśniającego przyczynę bałaganu i przepraszającego za to.

Funkcja gdzie ?

OK, więc jeśli nie obsługujemy tego jako wyrażenia inline w SQL, najbliższą alternatywą jaką mamy jest funkcja zdefiniowana przez użytkownika. I wiemy, że to niczego nie przyspieszy (chyba, że zdefiniujemy na nim indeks, tak jak w przypadku Oracle.) Jeśli musimy utworzyć funkcję, możemy lepiej zrobić to w kodzie, który wywołuje polecenie SQL.

I Ta funkcja może mieć pewne różnice w zachowaniu, w zależności od DBMS i wersji. (A shout out to all you Programiści Javy tak chętnie korzystają z dowolnego silnika bazodanowego zamiennie.)

Znajomość domeny

Możemy mieć specjalistyczną wiedzę na temat domeny dla kolumny, (czyli zbioru dopuszczalnych wartości wymuszonych dla kolumny. Możemy wiedzieć a priori , że wartości przechowywane w kolumnie nigdy nie będą zawierały znaku procentowego, podkreślenia lub par nawiasów. W takim razie zamieszczamy tylko krótki komentarz, że te przypadki są zakryte.

Wartości przechowywane w kolumnie mogą zezwalać na znaki % lub _, ale ograniczenie może wymagać, aby te wartości były unikane, być może przy użyciu zdefiniowanego znaku, tak że wartości są jak porównanie "bezpieczne". Ponownie, szybki komentarz na temat dozwolonego zestawu wartości, a w szczególności, który znak jest używany jako znak escape, i przejść z podejściem Joela Spolsky ' ego.

Ale, bez specjalistycznej wiedzy i gwarancji, ważne jest, abyśmy przynajmniej rozważyli Obsługa tych niejasnych przypadków narożnych i zastanów się, czy zachowanie jest rozsądne i "zgodnie ze specyfikacją".

Inne zagadnienia podsumowane

• SQL injection (biorąc to, co wydaje się być dostarczane przez użytkownika informacji, i włączenie tego w tekście SQL zamiast dostarczania ich za pomocą zmiennych bind. Używanie zmiennych bind nie jest wymagane, jest to tylko jedno wygodne podejście do udaremnienia z SQL injection. Istnieją inne sposoby radzenia sobie z tym:

• Optymalizator planuje skanowanie indeksów zamiast wyszukiwania indeksów, możliwe zapotrzebowanie na wyrażenie lub funkcję do ucieczki symboli wieloznacznych (możliwy indeks na wyrażeniu lub funkcji)

• Użycie wartości literalnych zamiast zmiennych bind wpływa skalowalność

Wniosek

Możesz przekazać parametr jako ciąg znaków

Więc masz ciąg

DECLARE @tags

SET @tags = ‘ruby|rails|scruffy|rubyonrails’

select * from Tags 
where Name in (SELECT item from fnSplit(@tags, ‘|’))
order by Count desc

Następnie wystarczy przekazać ciąg znaków jako 1 parametr.

Oto funkcja split, której używam.

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

Słyszałem, jak Jeff / Joel rozmawiali o tym dzisiaj w podcaście (odcinek 34, 2008-12-16 (MP3, 31 MB), 1 h 03 min 38 SEK - 1 h 06 min 45 SEK), a myślałem, że przypomniałem sobie, że Stack Overflow używa LINQ do SQL, ale może został porzucony. To samo jest w LINQ do SQL.

var inValues = new [] { "ruby","rails","scruffy","rubyonrails" };

var results = from tag in Tags
              where inValues.Contains(tag.Name)
              select tag;

W każdym razie, jeśli uruchomisz to w wysoce zalecanym LINQPad i uruchomisz to zapytanie, możesz wyświetlić rzeczywisty SQL wygenerowany przez dostawcę SQL LINQ. Pokaże Ci każdy wartości, które zostaną parametryzowane w klauzuli IN.

Jeśli dzwonisz z. NET, możesz użyć Dapper dot net :

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

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;

Jest to prawdopodobnie w połowie paskudny sposób, użyłem go raz, był raczej skuteczny.

W zależności od twoich celów może się przydać.

1. Utwórz tabelę temp z jedną kolumną.
2. INSERT każda wyszukana wartość do tej kolumny.
3. zamiast IN, możesz użyć standardowych reguł JOIN. (Elastyczność++)

Nigdy nie dotarłem do profilowania dokładnie tak, jak szybko było, ale w mojej sytuacji było to potrzebne.

Mamy funkcję, która tworzy zmienną tabelkową, do której można dołączyć:

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 

Więc:

@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

To jest obrzydliwe, ale jeśli masz gwarancję, że masz przynajmniej jedną, możesz zrobić:

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

Posiadanie w ('tag1', 'tag2', 'tag1',' tag1',' tag1') będzie łatwo zoptymalizowane przez SQL Server. Dodatkowo otrzymujesz bezpośredni indeks

Moim zdaniem najlepszym źródłem do rozwiązania tego problemu jest to, co zostało zamieszczone na tej stronie:

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

Użycie:

SELECT * FROM dbo.fnParseArray('a,b,c,d,e,f', ',')

Podziękowania dla: Dinakar Nethi

Przekazałbym parametr typu tabeli (ponieważ jest to SQL Server 2008) i wykonałbym where exists, lub połączenie wewnętrzne. Możesz również użyć XML, używając sp_xml_preparedocument, a następnie nawet indeksować tę tymczasową tabelę.

W SQL Server 2016+ możesz użyć SPLIT_STRING funkcja:

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

SELECT * 
FROM Tags
WHERE Name IN (SELECT [value] FROM STRING_SPLIT(@names, ','))
ORDER BY Count DESC;

Lub:

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

Przyjęta odpowiedź będzie oczywiście działać i jest to jedna z dróg, ale jest to anty-wzór.

E. Znajdź wiersze według listy wartości

Jest to zamiennik dla popularnych anty-wzorców, takich jak tworzenie dynamicznego ciągu SQL w warstwie aplikacji lub Transact-SQL, lub za pomocą LIKE operator:

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

Właściwym sposobem IMHO jest zapisanie listy w łańcuchu znaków (ograniczonym długością przez to, co obsługuje DBMS); jedyną sztuczką jest to, że (w celu uproszczenia przetwarzania) mam separator (przecinek w moim przykładzie) na początku i na końcu łańcucha. Chodzi o to, aby "normalizować w locie", zamieniając listę w tabelę jednokolumnową, która zawiera jeden wiersz na wartość. To pozwala włączyć

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

Do

W (wybierz ...)

Lub (rozwiązanie, które wolałbym) zwykłe połączenie, jeśli po prostu dodasz "distinct", aby uniknąć problemów z duplikatami wartości na liście.

Niestety, techniki cięcia sznurka są dość specyficzne dla produktu. Oto wersja 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;

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

I wersja 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, ',', ''));

(oczywiście, "pivot" musi zwracać tyle wierszy, ile Maksymalna liczba elementy, które możemy znaleźć na liście)

Jeśli masz SQL Server 2008 lub później użyłbym Parametr wartości tabeli .

Jeśli masz pecha utknąć na SQL Server 2005 możesz dodać CLR taką funkcję,

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

Które można wykorzystać w ten sposób,

declare @desiredTags nvarchar(MAX);
set @desiredTags = 'ruby,rails,scruffy,rubyonrails';

select * from Tags
where Name in [dbo].[Split] (',', @desiredTags)
order by Count desc

Podchodziłbym do tego domyślnie przekazując funkcję o wartości tabeli (która zwraca tabelę z łańcucha znaków) do warunku IN.

Oto kod UDF (dostałem go gdzieś ze Stack Overflow, nie mogę teraz znaleźć źródła)

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
  )

Gdy już to dostaniesz Twój kod będzie tak prosty jak ten:

select * from Tags 
where Name in (select s from dbo.split(';','ruby;rails;scruffy;rubyonrails'))
order by Count desc

Jeśli nie masz śmiesznie długiego ciągu, powinno to działać dobrze z indeksem tabeli.

W razie potrzeby można wstawić go do tabeli temp, zindeksuj go, a następnie uruchom połączenie...

Myślę, że jest to przypadek, gdy statyczne zapytanie nie jest po prostu drogą do zrobienia. Dynamicznie buduj listę klauzuli in, unikaj pojedynczych cudzysłowów i dynamicznie buduj SQL. W tym przypadku prawdopodobnie nie zobaczysz dużej różnicy z jakąkolwiek metodą ze względu na małą listę, ale najbardziej efektywną metodą jest wysłanie SQL dokładnie tak, jak jest napisane w Twoim poście. Myślę, że dobrym nawykiem jest pisanie go w najbardziej efektywny sposób, a nie robienie tego, co czyni najładniejszy kod, lub rozważanie go zła praktyka dynamicznego budowania SQL.

Widziałem, że funkcje dzielone wymagają dłuższego wykonania niż samo zapytanie w wielu przypadkach, gdy parametry stają się duże. Procedura składowana z parametrami wartościowanymi w tabeli w SQL 2008 jest jedyną inną opcją, którą rozważyłbym, chociaż prawdopodobnie będzie to wolniejsze w Twoim przypadku. TVP będzie prawdopodobnie szybsza tylko dla dużych list, Jeśli szukasz na głównym kluczu TVP, ponieważ SQL i tak zbuduje tymczasową tabelę dla listy (jeśli lista jest duża). Nie dowiesz się na pewno, dopóki tego nie przetestujesz.

Widziałem również procedury składowane, które miały 500 parametrów z domyślnymi wartościami null i miały WHERE Column1 IN (@Param1, @Param2, @Param3, ..., @ Param500). To spowodowało, że SQL zbudował tabelę tymczasową, zrobił sort/distinct, a następnie zrobił skanowanie tabeli zamiast wyszukiwania indeksów. To jest zasadniczo to, co byś zrobił, parametryzując to zapytanie, chociaż na tyle małej skali, że nie zrobi to zauważalnej różnicy. I Gorąco polecam, aby nie mieć NULL na listach IN, jakby to zostało zmienione na NOT IN nie będzie działać zgodnie z przeznaczeniem. Możesz dynamicznie budować listę parametrów, ale jedyną oczywistą rzeczą, którą zyskasz, jest to, że obiekty unikną pojedynczych cudzysłowów. To podejście jest również nieco wolniejsze na końcu aplikacji, ponieważ obiekty muszą analizować zapytanie, aby znaleźć parametry. Może być szybszy w SQL, ponieważ zapytania parametryzowane wywołują sp_prepare, sp_execute dla as wiele razy wykonujesz zapytanie, a następnie sp_unprepare.

Ponowne użycie planów wykonania dla procedur składowanych lub zapytań parametryzowanych może dać ci wzrost wydajności, ale zablokuje Cię do jednego planu wykonania określonego przez pierwsze zapytanie, które zostanie wykonane. To może być mniej niż idealne dla kolejnych zapytań w wielu przypadkach. W Twoim przypadku ponowne wykorzystanie planów wykonawczych będzie prawdopodobnie plusem, ale może to nie mieć żadnej różnicy, ponieważ przykład jest naprawdę prosty zapytanie.

Klify notes:

W Twoim przypadku wszystko, co robisz, czy to parametryzacja z ustaloną liczbą pozycji na liście (null, jeśli nie jest używana), dynamiczne budowanie zapytania z parametrami lub bez, czy korzystanie z procedur składowanych z parametrami wartości tabeli, nie będzie miało większego znaczenia. Jednak moje ogólne zalecenia są następujące:

Twój przypadek / proste zapytania z kilkoma parametrami:

Dynamiczny SQL, może z parametrami jeśli testowanie pokazuje lepszą wydajność.

Zapytania z planami wykonania wielokrotnego użytku, wywoływane wielokrotnie przez prostą zmianę parametrów lub jeśli zapytanie jest skomplikowane:

SQL z parametrami dynamicznymi.

Zapytania z dużymi listami:

Procedura składowana z parametrami wartości tabeli. Jeśli lista może się różnić o dużą ilość, użyj rekompilacji w procedurze składowanej lub po prostu użyj dynamicznego SQL bez parametrów, aby wygenerować nowy plan wykonania dla każdego zapytanie.

Może być możemy użyć XML tutaj:

    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)

Użyj następującej procedury składowanej. Używa niestandardowej funkcji podziału, którą można znaleźć tutaj .

 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

Innym możliwym rozwiązaniem jest przekazanie zmiennej liczby argumentów do procedury składowanej, przekazanie pojedynczego ciągu zawierającego nazwy, których szukasz, ale uczynienie ich unikalnymi, otaczając je znakiem''. Następnie użyj PATINDEX, aby znaleźć nazwy:

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

Oto technika, która odtwarza lokalną tabelę do użycia w łańcuchu zapytania. Robi to w ten sposób eliminuje wszystkie problemy z parsowaniem.

Łańcuch może być zbudowany w dowolnym języku. W tym przykładzie użyłem SQL, ponieważ był to oryginalny problem, który próbowałem rozwiązać. Potrzebowałem czystego sposobu przekazywania danych w tabeli w locie w łańcuchu, który zostanie wykonany później.

Użycie typu zdefiniowanego przez użytkownika jest opcjonalne. Tworzenie typu jest tworzone tylko raz i może być wykonane z wyprzedzeniem. W przeciwnym razie tylko dodaje pełny typ tabeli do deklaracji w łańcuchu.

Ogólny wzór jest łatwy do rozszerzenia i może być używany do przekazywania bardziej złożonych tabel.

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

Oto inna alternatywa. Wystarczy przekazać listę rozdzielaną przecinkami jako parametr łańcuchowy do procedury składowanej i:

CREATE PROCEDURE [dbo].[sp_myproc]
    @UnitList varchar(MAX) = '1,2,3'
AS
select column from table
where ph.UnitID in (select * from CsvToInt(@UnitList))

Oraz funkcja:

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

Jeśli wewnątrz klauzuli IN znajdują się łańcuchy znaków z oddzielonym przecinkiem (,), możemy użyć funkcji charindex do uzyskania wartości. Jeśli używasz. NET, możesz mapować za pomocą SqlParameters.

Skrypt 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

Możesz użyć powyższej instrukcji w kodzie. NET i odwzorować parametr za pomocą SqlParameter.

Fiddler demo

EDIT: Utwórz tabelę o nazwie SelectedTags używając następujących scenariusz.

Skrypt 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

Dla zmiennej liczby argumentów, takich jak ten, jedynym sposobem, o którym wiem, jest jawne wygenerowanie SQL lub zrobienie czegoś, co wiąże się z wypełnieniem tymczasowej tabeli elementami, które chcesz i połączeniem z tabelą tymczasową.

W ColdFusion robimy tylko:

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

Używam bardziej zwięzłej wersji z góry głosowanej odpowiedzi :

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

Robi pętlę przez parametry znacznika dwa razy; ale to nie ma znaczenia przez większość czasu(nie będzie to twoje wąskie gardło; jeśli tak jest, rozwiń pętlę).

Jeśli naprawdę interesuje Cię wydajność i nie chcesz powtarzać pętli dwa razy, oto mniej piękna wersja:

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

W SQL Server 2016+ inną możliwością jest użycie OPENJSON funkcja.

To podejście jest blogowane w OPENJSON-jeden z najlepszych sposobów, aby wybrać wiersze według listy ids.

Pełny przykład pracy poniżej

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 

Mam odpowiedź, która nie wymaga UDF, XML Ponieważ IN akceptuje polecenie select np. SELECT * FROM Test where Data IN (SELECT Value FROM TABLE)

Naprawdę potrzebujesz tylko sposobu, aby przekształcić łańcuch znaków w tabelę.

Można to zrobić za pomocą rekurencyjnego CTE lub zapytania z tabelą liczb (lub wzorcem..spt_value)

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

Oto kolejna odpowiedź na ten problem.

(nowa wersja opublikowana 6/4/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;
    }

Oto cross-post do rozwiązania tego samego problemu. Bardziej wytrzymały niż ograniczniki zarezerwowane-zawiera tablice specjalne i zagnieżdżone oraz rozumie wartości Null i puste tablice.

C# & T-SQL string [] Pack/Unpack utility functions

Możesz następnie dołączyć do funkcji o wartości tabelarycznej.