Paramétrer un SQL dans la clause

vous pouvez paramétrer chaque valeur , donc quelque chose comme:

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

qui vous donnera:

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"

Non, ceci n'est pas ouvert à injection SQL . Le seul texte injecté dans CommandText n'est pas basé sur l'entrée de l'utilisateur. Il est uniquement basé sur le préfixe hardcodé "@tag", et l'index d'un tableau. L'indice toujours être un entier, n'est pas généré par l'utilisateur, et est sûr.

les valeurs saisies par l'utilisateur sont toujours incluses dans les paramètres, il n'y a donc pas de vulnérabilité.

Edit:

préoccupations D'Injection mis à part, prenez soin de noter que la construction du texte de commande pour accommoder un nombre variable de paramètres (comme ci-dessus) empêche la capacité du serveur SQL de tirer avantage des requêtes mises en cache. Le résultat net est que vous perdez presque certainement la valeur d'utiliser des paramètres dans le première place (au lieu de simplement insérer les chaînes de prédicat dans le SQL lui-même).

non pas que les plans de requête cache ne sont pas valables, mais IMO cette requête n'est pas assez compliquée pour voir beaucoup d'avantages de celui-ci. Alors que les coûts de compilation peuvent approcher (ou même dépasser) les coûts d'exécution, vous parlez encore des millisecondes.

si vous avez assez de RAM, Je m'attendrais à ce que SQL Server cache probablement un plan pour les comptes courants de les paramètres ainsi. Je suppose que vous pouvez toujours ajouter cinq paramètres, et laisser les étiquettes non spécifiées être NULL - le plan de requête devrait être le même, mais il semble assez laid pour moi et je ne suis pas sûr que cela vaudrait la peine de la micro-optimisation (bien que, sur le débordement de la pile - il peut très bien en valoir la peine).

aussi, SQL Server 7 et plus tard va auto-paramétrer les requêtes , donc l'utilisation de paramètres n'est pas vraiment nécessaire du point de vue de la performance - il est, cependant, critique du point de vue de la sécurité - en particulier avec les données saisies par l'utilisateur comme ceci.

pour SQL Server 2008, vous pouvez utiliser un table valued parameter . C'est un peu de travail, mais il est sans doute plus propre que mon autre méthode .

Premièrement, vous devez créer un type

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

alors, votre ADO.NET le code ressemble à ceci:

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

la question originale était " comment paramétrer une requête ..."

Permettez-moi de dire ici, que c'est pas une réponse à la question originale. Il y a déjà quelques démonstrations de cela dans d'autres bonnes réponses.

Avec cela dit, aller de l'avant et le drapeau de cette réponse, downvote, le marquer comme pas de réponse... fais ce que tu crois être juste.

voir la réponse de Mark Brackett pour la réponse que j'ai préférée(et 231 autres). L'approche donnée dans sa réponse permet 1) une utilisation efficace des variables de liaison et 2) des prédicats qui sont sargeables.

réponse sélectionnée

ce que je veux aborder ici est l'approche donnée dans la réponse de Joel Spolsky, la réponse" choisi " comme la bonne réponse.

L'approche de Joel Spolsky est intelligente. Et il fonctionne raisonnablement, il va présenter un comportement prévisible et des performances prévisibles, étant donné les valeurs "normales", et avec les cas de bord normatif, tels que NULL et la chaîne vide. Et il peut être suffisant pour une application particulière.

mais en termes de généralisation de cette approche, considérons aussi les cas de coin plus obscurs, comme lorsque la colonne Name contient un caractère Joker (comme reconnu par le prédicat similaire.) Le caractère Joker que je vois le plus % (un signe de pourcentage.). Alors, parlons-en maintenant, et plus tard, parlons d'autres affaires.

Quelques problèmes avec le caractère %

Considérer une valeur de Nom de 'pe%ter' . (Pour les exemples ici, j'utilise une valeur de chaîne littérale à la place du nom de la colonne.) Une ligne avec une valeur de Nom de `pe%ter " seraient retournés par une requête de la forme:

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

Mais cette même ligne est-ce que et non sera retourné si l'ordre des termes de recherche est inversé:

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

Le comportement que nous observons est bizarre. Changer l'ordre des termes de recherche dans la liste change le résultat.

Il va presque sans dire que nous ne voulons pas pe%ter pour correspondre au beurre d'arachide, peu importe combien il l'aime.

coin Obscur de cas

(Oui, je conviens qu'il s'agit d'un cas obscur. Probablement l'un qui n'est pas susceptible d'être testé. On ne s'attend pas à un joker dans la valeur d'une colonne. On peut supposer que l'application empêche une telle valeur d'être stocké. Mais d'après mon expérience, j'ai rarement vu une contrainte de base de données qui interdisait spécifiquement les caractères ou les motifs qui seraient considérés comme des caractères génériques du côté droit d'un opérateur de comparaison LIKE .

correction un trou

une façon de corriger ce trou est d'échapper au caractère % . (Pour ceux qui ne sont pas familiers avec la clause escape sur l'opérateur, voici un lien vers la documentation du serveur SQL .

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

maintenant nous pouvons égaler le % littéral. Bien sûr, quand on a un nom de colonne, on va devoir échapper dynamiquement au Joker. Nous pouvons utiliser la fonction REPLACE pour trouver occurrences du % personnage et insérer une barre oblique inverse devant chacun, comme ceci:

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

pour résoudre le problème avec le % Joker. Presque.

Échapper au échapper

nous reconnaissons que notre solution a introduit un autre problème. Le personnage de l'évasion. Nous voyons que nous allons aussi avoir besoin d'échapper à tous les événements de fuite caractère lui-même. Ce temps, nous utilisons les ! comme le caractère d'évasion:

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

Le trait de soulignement trop

maintenant que nous sommes sur un rouleau, nous pouvons ajouter un autre REPLACE gérer le Joker de soulignement. Et juste pour le plaisir, cette fois, nous utiliserons $ comme personnage d'évasion.

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

je préfère cette approche à l'évasion parce qu'il fonctionne dans Oracle et MySQL ainsi que SQL Server. (J'utilise habituellement le \ backslash comme le caractère escape, puisque c'est le caractère que nous utilisons dans les expressions régulières. Mais pourquoi être contraint par convention!

Ces satanés crochets

SQL Server permet également de traiter les caractères génériques comme des caractères littéraux en les mettant entre parenthèses [] . Donc nous n'avons pas encore fini de réparer, au moins pour SQL Server. Comme les paires de crochets ont une signification spéciale, nous aurons besoin d'échapper à ceux aussi bien. Si nous gérer pour échapper correctement les crochets, alors au moins nous n'aurons pas à se soucier du trait d'Union - et le carat ^ dans les crochets. Et nous pouvons laisser n'importe quels caractères % et _ dans les crochets échappés, puisque nous aurons pratiquement désactivé la signification spéciale des crochets.

Trouver des paires de crochets ne devrait pas être difficile. C'est un peu plus difficile que de gérer les occurrences de singleton % et _. (Note qu'il ne suffit pas d'échapper à toutes les occurrences de crochets, parce qu'un crochet simple est considéré comme un littéral, et n'a pas besoin d'être échappé. La logique devient un peu plus floue que ce que je peux supporter sans faire plus de tests.)

Inline expression devient malpropre

cette expression en ligne dans le SQL devient plus longue et plus laide. Nous pouvons probablement le faire fonctionner, mais le ciel aide l'âme pauvre que vient derrière et doit le déchiffrer. Comme je suis un grand fan de Inline expressions, je suis enclin à ne pas utiliser un ici, principalement parce que je ne veux pas avoir à laisser un commentaire expliquant la raison de la pagaille, et de s'en excuser.

une fonction où ?

Ok, donc, si nous ne traitons pas cela comme une expression en ligne dans le SQL, l'alternative la plus proche que nous ayons est une fonction définie par l'utilisateur. Et nous savons que de ne pas la vitesse les choses en haut de n'importe quel (à moins que nous puissions définir un index dessus, comme nous pourrions le faire avec Oracle.) Si nous devons créer une fonction, nous ferions mieux de le faire dans le code qui appelle la déclaration SQL.

et cette fonction peut avoir des différences de comportement, dépendant du SGBD et de la version. (Un shout out à tous les développeurs Java tellement envie d'être en mesure d'utiliser tout moteur de base de données de façon interchangeable.)

connaissances du domaine

Nous pouvons avoir des connaissances spécialisées du domaine de la colonne, (c'est l'ensemble des valeurs admissibles appliquée pour la colonne. Nous pouvons savoir a priori que les valeurs stockées dans la colonne ne contiendront jamais un signe de pourcentage, un underscore, ou des paires de crochets. Dans ce cas, nous indiquons simplement que ces cas sont couverts.

les valeurs stockées dans la colonne peuvent permettre % ou _ caractères, mais une contrainte peut exiger que ces valeurs soient échappées, peut-être en utilisant un caractère défini, de sorte que les valeurs sont comme comparaison "sûr". Encore une fois, un commentaire rapide sur l'ensemble autorisé de valeurs, et en particulier quel caractère est utilisé comme un caractère d'évasion, et aller avec L'approche de Joel Spolsky.

mais, en l'absence de connaissances spécialisées et d'une garantie, il est important pour nous d'au moins envisager de traiter ces cas obscurs coin, et de se demander si le comportement est raisonnable et "selon la spécification".

autres questions récapitulées

je crois que d'autres ont déjà suffisamment souligné certains des autres sujets de préoccupation communément considérés:

• injection SQL (en prenant ce qui semble être des informations fournies par l'utilisateur, et en incluant cela dans le texte SQL plutôt que de fournir eux par le biais de lier les variables. Utiliser les variables de bind n'est pas nécessaire, c'est juste une approche pratique pour contrecarrer avec L'injection SQL. Il y a d'autres façons de le traiter:

• optimiseur de plan à l'aide d'analyse d'index plutôt que de l'indice vise, au besoin éventuel d'une expression ou de la fonction pour échapper les caractères génériques (indice possible sur l'expression ou de la fonction)

• utilisant des valeurs littérales au lieu de bind variables impacts évolutivité

Conclusion

j'aime L'approche de Joel Spolsky. Il est intelligent. Et il fonctionne.

mais dès que je l'ai vu, j'ai immédiatement vu un problème potentiel avec elle, et ce n'est pas ma nature de la laisser glisser. Je ne veux pas critiquer les efforts des autres. Je sais que beaucoup de développeurs prennent leur travail très personnellement, parce qu'ils investissent tellement dans et ils tiennent tellement à son sujet. Alors s'il vous plaît comprenez, ce n'est pas une attaque personnelle. Ce que j'identifie ici, c'est le type de problème qui apparaît dans la production plutôt que dans les essais.

Oui, je suis allé bien loin de la question originale. Mais où d'autre laisser cette note concernant ce que je considère comme une question importante avec la réponse "choisie" pour une question?

vous pouvez passer le paramètre comme une chaîne de caractères

donc vous avez la chaîne

DECLARE @tags

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

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

alors tout ce que vous avez à faire est de passer la chaîne comme paramètre 1.

Voici la fonction split que j'utilise.

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

J'ai entendu Jeff/Joel en parler sur le podcast d'aujourd'hui ( Épisode 34 , 2008-12-16 (MP3, 31 MB), 1 h 03 min 38 secs - 1 h 06 min 45 secs), et j'ai cru me rappeler que le débordement de la cheminée utilisait LINQ à SQL , mais peut-être qu'il a été abandonné. C'est la même chose à LINQ à SQL.

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

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

C'est ça. Et, oui, LINQ regarde déjà assez en arrière, mais la clause Contains semble extra en arrière de moi. Lorsque j'ai eu à faire une requête similaire pour un projet au travail, j'ai naturellement essayé de faire mal en faisant une jointure entre le tableau et la table SQL Server, figurant l'LINQ to SQL traducteur serait assez intelligent pour gérer la traduction d'une certaine manière. Il ne l'a pas fait, mais il a fourni un message d'erreur qui était descriptif et m'a indiqué l'utilisation de contient .

de toute façon, si vous exécutez ceci dans le fortement recommandé LINQPad , et exécuter cette requête, vous pouvez voir le SQL réel que le fournisseur de LINQ SQL généré. Il vous montrera chacune des valeurs paramétrées dans une clause IN .

si vous appelez de .NET, vous pouvez utiliser 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});

ici Dapper pense, donc vous n'avez pas à. Quelque chose de similaire est possible avec LINQ à SQL , bien sûr:

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;

c'est peut-être une façon mi-méchante de le faire, je l'ai utilisé une fois, était plutôt efficace.

selon vos objectifs, il pourrait être utile.

1. créer une table de température avec une colonne.
2. INSERT chaque valeur dans cette colonne.
3. au lieu d'utiliser un IN , vous pouvez alors juste utiliser votre norme JOIN règles. ( Flexibilité++ )

cela a un peu de flexibilité ajoutée dans ce que vous pouvez faire, mais il est plus adapté pour les situations où vous avez une grande table à interroger, avec une bonne indexation, et vous voulez utiliser la liste paramétrée plus d'une fois. Évite d'avoir à l'exécuter deux fois et ont tous l'assainissement fait manuellement.

Je n'ai jamais eu le temps d'établir le profil exactement comme rapide il était, mais dans ma situation, il était nécessaire.

nous avons une fonction qui crée une variable de table que vous pouvez joindre à:

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

c'est grossier, mais si vous êtes assuré d'en avoir au moins un, vous pouvez le faire:

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

à mon avis, la meilleure source pour résoudre ce problème, est ce qui a été affiché sur ce site:

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

utiliser:

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

crédits pour: Dinakar Nethi

je passerais un paramètre de type de table (puisque c'est SQL Server 2008 ), et je ferais un where exists , ou une jointure interne. Vous pouvez également utiliser XML, en utilisant sp_xml_preparedocument , et même indexer cette table temporaire.

dans SQL Server 2016+ vous pouvez utiliser SPLIT_STRING fonction:

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

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

ou:

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

Le accepté de répondre volonté de travaux de cours et c'est l'un de la manière de faire, mais il est anti-modèle.

E. Trouver des lignes par liste de valeurs

il s'agit du remplacement d'un anti-pattern courant tel que la création d'une chaîne SQL dynamique dans la couche application ou Transact-SQL, ou en utilisant un opérateur similaire:

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

la bonne façon IMHO est de stocker la liste dans une chaîne de caractères (limitée en longueur par ce que le SGBD supporte); le seul truc est que (afin de simplifier le traitement) j'ai un séparateur (une virgule dans mon exemple) au début et à la fin de la chaîne. L'idée est de "normaliser à la volée", en tournant la liste dans une table d'une colonne qui contient une ligne par valeur. Cela vous permet de tourner

(ct1,ct2, ct3 ... ctn)

en

in (select ...)

ou (la solution que je préférerais probablement) une jointure régulière, si vous ajoutez juste un" distinct " pour éviter les problèmes avec les valeurs dupliquées dans la liste.

malheureusement, les techniques pour trancher une corde sont assez spécifiques du produit. Voici la version du serveur SQL:

 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;

de La version 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, ',', ''))));

et la version 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, ',', ''));

(bien sûr," pivot " doit retourner autant de lignes que le nombre maximum de les éléments que nous pouvons trouver dans la liste)

si vous avez SQL Server 2008 ou plus tard, j'utiliserais un table Valued Parameter .

si vous avez la malchance d'être bloqué sur SQL Server 2005 vous pouvez ajouter un CLR fonction comme celle-ci,

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

que vous pourriez utiliser comme ceci,

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

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

j'approcherais ceci par défaut en passant une fonction de valeur de table (qui renvoie une table à partir d'une chaîne de caractères) à la condition IN.

voici le code pour L'UDF (Je l'ai obtenu de Stack Overflow somewhere, Je ne peux pas trouver la source en ce moment)

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
  )

une fois que vous avez obtenu ceci votre code serait aussi simple que ceci:

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

sauf si vous avez une chaîne ridiculement longue, cela devrait fonctionner bien avec les index de la table.

Si nécessaire, vous pouvez l'insérer dans une table temporaire, index, puis exécutez une jointure...

je pense que c'est un cas où une requête statique n'est tout simplement pas le chemin à suivre. Compilez dynamiquement la liste pour votre clause in, échappez à vos guillemets simples, et compilez dynamiquement SQL. Dans ce cas, vous ne verrez probablement pas beaucoup de différence avec n'importe quelle méthode en raison de la petite liste, mais la méthode la plus efficace est vraiment d'envoyer le SQL exactement comme il est écrit dans votre post. Je pense que c'est une bonne habitude de l'écrire de la manière la plus efficace, plutôt que de faire ce qui fait le plus beau code, ou considérez que c'est une mauvaise pratique de construire dynamiquement SQL.

j'ai vu que les fonctions split prennent plus de temps à exécuter que la requête elle-même dans de nombreux cas où les paramètres deviennent grands. Une procédure stockée avec des paramètres de valeur de table en SQL 2008 est la seule autre option que je considérerais, bien que ce sera probablement plus lent dans votre cas. TVP ne sera probablement plus rapide pour les grandes listes que si vous cherchez sur la clé primaire de la TVP, parce que SQL construira un temporaire tableau pour la liste de toute façon (si la liste est grande). Vous n'en saurez rien si vous ne le testez pas.

j'ai aussi vu des procédures stockées qui avaient 500 paramètres avec des valeurs par défaut nulles, et où Column1 dans (@Param1, @Param2, @Param3, ..., @Param500). Cela a amené SQL à construire une table temp, à faire un sort/distinct, puis à faire un scan de table au lieu d'une recherche d'index. C'est essentiellement ce que vous feriez en paramétrant cette requête, bien que sur une échelle assez petite qu'il ne fera pas une différence notable. Je recommande fortement de ne pas avoir NULL dans vos listes IN, comme si cela est changé à un pas dans elle n'agira pas comme prévu. Vous pourriez construire dynamiquement la liste de paramètres, mais la seule chose évidente que vous gagneriez est que les objets échapperaient aux guillemets simples pour vous. Cette approche est également légèrement plus lente à la fin de l'application puisque les objets doivent analyser la requête pour trouver les paramètres. Il peut ou ne peut pas être plus rapide sur SQL, comme les requêtes paramétrées appellent sp_prepare, sp_execute pour autant de fois que vous exécutez la requête, suivi par sp_unprepare.

la réutilisation des plans d'exécution pour les procédures stockées ou les requêtes paramétrées peut vous donner un gain de performance, mais elle vous enfermera dans un plan d'exécution déterminé par la première requête qui est exécutée. Cela peut être loin d'être idéal pour les requêtes ultérieures dans de nombreux cas. Dans votre cas, la réutilisation des plans d'exécution sera probablement un plus, mais il pourrait ne pas faites toute différence car l'exemple est une requête très simple.

Cliffs notes:

pour votre cas tout ce que vous faites, que ce soit la paramétrisation avec un nombre fixe d'éléments dans la liste (nul si non utilisé), la construction dynamique de la requête avec ou sans paramètres, ou en utilisant des procédures stockées avec des paramètres de valeur de table ne fera pas beaucoup de différence. Cependant, mes recommandations générales sont les suivantes:

Votre cas/requêtes simples avec peu de paramètres:

SQL dynamique, peut-être avec des paramètres si les essais montrent une meilleure performance.

requêtes avec des plans d'exécution réutilisables, appelées plusieurs fois en changeant simplement les paramètres ou si la requête est compliquée:

SQL avec paramètres dynamiques.

requêtes avec de grandes listes:

procédure stockée avec paramètres de valeur de table. Si la liste peut varier par une grande quantité d'utilisation recompiler sur la procédure stockée, ou tout simplement utiliser SQL dynamique sans paramètres pour générer un nouveau plan d'exécution pour chaque requête.

peut - être que nous pouvons utiliser XML ici:

    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)

utilisez la procédure stockée suivante. Il utilise une fonction split personnalisée, qui peut être trouvé ici .

 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

une autre solution possible est au lieu de passer un nombre variable d'arguments à une procédure stockée, passer une chaîne simple contenant les noms que vous recherchez, mais les rendre uniques en les entourant avec '<>'. Utilisez alors PATINDEX pour trouver les noms:

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

Voici une technique qui recrée une table locale à utiliser dans une chaîne de requête. Le faire de cette façon élimine tous les problèmes d'analyse.

la chaîne peut être construite dans n'importe quelle langue. Dans cet exemple, J'ai utilisé SQL car c'était le problème original que j'essayais de résoudre. J'avais besoin d'un moyen propre pour passer dans les données de table à la volée dans une chaîne à exécuter plus tard.

utilisant un type défini par l'utilisateur est facultatif. Créer le type n'est créé qu'une seule fois et peut être fait à l'avance. Sinon, il suffit d'ajouter un type de table complet à la déclaration dans la chaîne.

le modèle général est facile à étendre et peut être utilisé pour passer des tables plus complexes.

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

Voici une autre alternative. Il suffit de passer une liste délimitée par une virgule comme paramètre string à la procédure stockée et:

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

et la fonction:

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

si nous avons des chaînes stockées dans la clause IN avec la virgule(,) délimitée, nous pouvons utiliser la fonction charindex pour obtenir les valeurs. Si vous utilisez .NET, alors vous pouvez cartographier avec SqlParameters.

Script 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

vous pouvez utiliser la déclaration ci-dessus dans votre code .NET et mapper le paramètre avec SqlParameter.

Fiddler démo

EDIT: Créez la table appelée SelectedTags en utilisant le script suivant.

script 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

pour un nombre variable d'arguments comme celui-ci la seule façon dont je suis conscient est soit de générer le SQL explicitement ou de faire quelque chose qui implique de peupler une table temporaire avec les éléments que vous voulez et de se joindre contre la table temp.

Dans ColdFusion nous venons de faire:

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

j'utilise une version plus concise de haut voté réponse :

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

il fait boucle à travers les paramètres de tag deux fois; mais cela n'a pas d'importance la plupart du temps (ce ne sera pas votre goulot; si c'est le cas, déroulez la boucle).

si vous êtes vraiment intéressé par la performance et ne voulez pas itérer à travers la boucle deux fois, voici une version moins belle:

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

dans SQL Server 2016+ une autre possibilité est d'utiliser la fonction OPENJSON .

Cette approche est blogué au sujet de OPENJSON - l'une des meilleures façons de sélectionner des lignes par liste d'id .

plein travaillé exemple ci-dessous

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 

j'ai une réponse qui ne nécessite pas de UDF, XML Parce que dans accepte une déclaration select par exemple, sélectionner * à partir de L'essai où Données IN (sélectionner la valeur dans la TABLE)

Vous avez vraiment besoin d'un moyen de convertir la chaîne en un tableau.

cela peut être fait avec un CTE récursif, ou une requête avec une table de nombres (ou Master..spt_value)

Voici la version 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);

Voici une autre réponse à ce problème.

(nouvelle version postée le 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;
    }

santé.

voici un cross-post à une solution au même problème. Plus robuste que les délimiteurs réservés - inclut les réseaux échappés et imbriqués, et comprend les NULLs et les réseaux vides.

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

Vous pouvez ensuite joindre à la fonction table.