Paramétrer un SQL À 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, ce n'est pas ouverte à L'injection SQL. La seule injecté texte dans CommandText n'est pas basée sur la saisie de l'utilisateur. C'est uniquement sur la base des codé en dur "@tag" préfixe, et l'indice d'un tableau. L'indice toujours être un entier, n'est pas généré par l'utilisateur, et est sûr.

L'utilisateur saisi les valeurs sont toujours fourré dans les paramètres, donc il n'y a aucune vulnérabilité n'.

Edit:

Injection préoccupations de côté, prenez bien soin de noter que la construction de la commande de texte à accueillir un nombre variable de paramètres (comme ci-dessus) empêchent de SQL server est capable de prendre avantage de la mise en cache de requêtes. Le résultat net est que vous avez presque certainement perdre de la valeur à l'aide des paramètres en premier lieu (par opposition à simplement d'insérer le prédicat des chaînes dans le SQL lui-même).

Pas mis en cache les plans de requête ne sont pas utiles, mais l'OMI cette requête n'est pas presque assez compliqué de voir beaucoup d'avantages. Alors que les coûts de compilation peut approcher (et même dépasser) les frais d'exécution, vous êtes encore à parler de quelques millisecondes.

Si vous avez suffisamment de RAM, je m'attends à SQL Serveur serait probablement cache un plan pour le compte de paramètres. Je suppose que vous pouvez toujours ajouter des cinq paramètres, et de laisser l'une quelconque des balises être NUL - le plan de requête doit être le même, mais il semble assez laid pour moi et je ne suis pas sûr que ça en vaut la micro-optimisation (bien que, sur Stack Overflow - il peut très bien être la peine).

Aussi, SQL Server 7 et les versions ultérieures auto-paramétrer les requêtes, donc, à l'aide de paramètres n'est pas vraiment nécessaire d'un point de vue des performances, - il est, cependant, critique à partir d'un point de vue sécurité - en particulier avec l'utilisateur saisie des données de cette façon.

La question d'origine était "Comment dois-je paramétrer une requête ..."

Permettez-moi de préciser, ici, que c'est pas une réponse à la question d'origine. Il y a déjà quelques démonstrations de ce que dans les autres bonnes réponses.

Avec cela dit, aller de l'avant et le drapeau de cette réponse, downvote, le marquer comme non pas une réponse... faire ce que vous croyez est juste.

Voir la réponse de Mark Brackett pour la meilleure réponse que j'ai (et 231 autres) upvoted. L'approche adoptée dans sa réponse permet 1) pour l'utilisation efficace de lier les variables, et 2) pour les prédicats qui sont sargable.

Réponse sélectionnée

Ce que je veux aborder ici est l'approche adoptée dans Joel Spolsky de la réponse, la réponse "sélectionné" que le droit de réponse.

Joel Spolsky approche est intelligent. Et il fonctionne raisonnablement, il va présentent un comportement prévisible et des performances prévisibles, compte tenu de la "normale" valeurs", et avec le normatif des cas limites, comme NULLE, et la chaîne vide. Et il peut être suffisant pour une application particulière.

Mais en termes de généraliser cette approche, mais nous considérons également la plus coin obscur cas, comme lorsque le Name colonne contient un caractère générique (tel que reconnu par le prédicat LIKE.) Le caractère générique je vois le plus couramment utilisé est % (un signe de pourcentage.). Donc, nous allons en traiter ici, maintenant, et plus tard passer à d'autres cas.

Quelques problèmes avec le caractère %

Considérer une valeur de Nom de 'pe%ter'. (Pour les exemples ici, j'utilise un littéral de chaîne de valeur en lieu et 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 la même ligne pas être retourné si l'ordre des mots est inversé:

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

Le comportement que nous observons est bizarre. Modification de l'ordre des termes de recherche dans la liste des modifications de l'ensemble de résultats.

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

Coin obscur cas

(Oui, je suis d'accord que c'est une obscure affaire. Probablement l'un qui n'est pas susceptible d'être testé. On ne s'attendrait pas d'un caractère générique dans la valeur d'une colonne. On peut supposer que l'application empêche une telle valeur d'être stocké. Mais dans mon expérience, j'ai rarement vu une contrainte de base de données spécifiquement des caractères non autorisés ou des motifs qui seraient considérés comme des caractères génériques sur le côté droit d'un LIKE opérateur de comparaison.

Bouchant un trou

Une approche à colmater ce trou est d'échapper à la % caractère générique. (Pour quiconque n'est pas familier avec la clause de sauvegarde sur l'opérateur, voici un lien vers le La documentation de SQL Server.

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

Maintenant, nous pouvons comparer les littéral %. Bien sûr, quand on a un nom de colonne, nous allons avoir besoin de dynamique d'échapper le caractère générique. Nous pouvons utiliser la REPLACE fonction pour trouver des occurrences de la %personnage et insérer une barre oblique inverse devant chacun, comme ceci:

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

Si cela résout le problème avec le générique%. Presque.

Échapper au échapper

Nous reconnaissons que notre solution a présenté un autre problème. Le caractère d'échappement. Nous voyons que nous allons aussi avoir besoin d'échapper à toutes les occurrences d'un caractère d'échappement lui-même. Cette fois, nous utilisons la ! comme le caractère d'échappement:

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

Le trait de soulignement trop

Maintenant que nous sommes sur une lancée, nous pouvons ajouter une autre REPLACE gérer le trait de soulignement générique. Et juste pour le fun, cette fois, nous allons utiliser $ comme caractère d'échappement.

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

Je préfère cette approche s'échapper, car il travaille dans Oracle et MySQL ainsi que SQL Server. (J'ai l'habitude d'utiliser le \ barre oblique inverse comme caractère d'échappement, puisque c'est le personnage que nous utilisons dans les expressions régulières. Mais pourquoi être contraint par la convention!

Ces satanés crochets

SQL Server permet également des caractères génériques pour être traités comme des littéraux en les enfermant dans des crochets []. Nous ne sommes donc pas fait de fixation encore, 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 parvenons à sortir correctement des parenthèses, puis au moins on n'aura pas à s'embêter avec le trait d'union - et le carat ^ à l'intérieur des parenthèses. Et nous pouvons laisser tout %et _ entre parenthèses échappé, puisque nous allons avoir désactivé la signification spéciale des crochets.

Trouver des paires de crochets ne devrait pas être difficile. C'est un peu plus difficile de gérer les occurrences de singleton % "et"_". (Notez qu'il ne suffit pas de juste d'échapper à toutes les occurrences de crochets, car un singleton support est considéré comme un littéral, et n'a pas besoin d'être échappé. La logique est un peu plus floue que je peux gérer sans courir le plus de cas de tests.)

Inline expression devient malpropre

Que inline expression dans le SQL est de plus en plus longues et de plus laid. On peut probablement le faire fonctionner, mais ciel, à l'aide de la pauvre âme qui vient par derrière et a de le déchiffrer. En tant que fan je suis pour les expressions, je suis enclin à ne pas utiliser une ici, principalement parce que je ne veux pas avoir à laisser un commentaire expliquant la raison de la pagaille, et s'excuser pour cela.

Une fonction où ?

Bon, alors, si nous ne gérons pas que comme une ligne d'expression dans le SQL, le plus proche de la solution que nous avons est une fonction définie par l'utilisateur. Et nous savons que de ne pas accélérer les choses tout (à moins que nous pouvons définir un index sur elle, comme nous avons pu avec Oracle.) Si nous avons pour créer une fonction, nous pourrions mieux faire que dans le code qui appelle l'instruction SQL.

Et qu'une fonction peut avoir quelques différences dans le comportement, dépendant du SGBD et 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.)

La connaissance du domaine

Nous pouvons avoir des connaissances du domaine pour la colonne, (c'est l'ensemble des valeurs admissibles appliquée pour la colonne. Nous savons a priori que les valeurs stockées dans la colonne ne comprendront jamais un signe de pourcentage, un trait de soulignement, ou support de paires. Dans ce cas, nous avons simplement inclure un commentaire rapide que ces cas sont couverts.

Les valeurs stockées dans la colonne peut autoriser % ou _ personnages, mais une contrainte peut exiger de ces valeurs pour être échappé, peut-être l'aide d'un caractère définis, tels que les valeurs sont des valeurs de comparaison "safe". Encore une fois, un rapide commentaire sur le jeu autorisé de valeurs, et en particulier le caractère qui est utilisé comme un caractère d'échappement, et d'aller avec Joel Spolsky de l'approche.

Mais, en l'absence de la connaissance spécialisée et d'une garantie, il est important pour nous d'envisager la gestion de coin obscur de cas, et d'examiner si le comportement est raisonnable et "par le cahier des charges".

---

D'autres questions récapitulé

Je crois que d'autres ont déjà suffisamment souligné quelques-uns des autres communément considérés comme des domaines de préoccupation:

• L'injection SQL (prendre ce qui semble être fourni par l'utilisateur de l'information, et notamment que, dans le texte SQL plutôt que de leur fournir par le biais de lier les variables. À l'aide de variables de liaison n'est pas obligatoire, c'est juste une approche pratique pour contrecarrer avec injection SQL. Il y a d'autres façons de traiter avec elle:

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

• l'utilisation de valeurs littérales en place de lier les variables impacts de l'évolutivité

---

Conclusion

J'aime Joel Spolsky de l'approche. Il est intelligent. Et il fonctionne.

Mais dès que j'ai vu, j'ai tout de suite vu un problème potentiel avec elle, et ce n'est pas ma nature de le laisser glisser. Je ne veux pas être critique sur 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. Donc, s'il vous plaît comprendre, ce n'est pas une attaque personnelle. Ce que je suis à identifier ici est le type de problème que l'on rencontre dans la production plutôt que de tester.

Oui, je suis allé au-delà de la question d'origine. Mais où d'autre de laisser cette remarque à propos de ce que je considère être un problème important avec les "sélectionnés" répondre à une question?

J'ai entendu Jeff/Joel parler à ce sujet sur le podcast d'aujourd'hui (épisode 34, 2008-12-16 (MP3, 31 MO), 1 h 03 min 38 sec - 1 h 06 min 45 sec), et j'ai pensé que je l'ai rappelé Pile Dépassement de l'aide LINQ à SQL, mais peut-être qu'il a été abandonné. Voici la même chose en LINQ à SQL.

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

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

Que c'est. Et, oui, LINQ regarde déjà vers l'arrière assez, mais le Contains clause semble extra en arrière pour 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 à SQL traducteur serait assez intelligent pour gérer la traduction d'une certaine manière. Il n'a pas, mais il n'a fournir un message d'erreur est descriptive et m'a orienté vers l'aide Contient.

De toute façon, si vous exécutez ce dans la fortement recommandé LINQPad, et exécuter cette requête, vous pouvez visualiser la réelle SQL le SQL, LINQ fournisseur généré. Il va vous montrer chacune des valeurs arriver paramétrée dans un IN clause.

C'est peut-être une demi-méchant manière de faire, je l'ai utilisé une fois, a été plutôt efficace.

En fonction de vos objectifs, il pourrait être utile.

1. Créer un de la table temporaire avec une colonne.
2. INSERT chaque valeur dans cette colonne.
3. Au lieu d'utiliser un IN, vous pouvez simplement utiliser votre standard JOIN règles. ( Flexibilité++ )

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

Je n'ai jamais eu l'occasion de profilage exactement comment rapide il était, mais dans ma situation, il a été nécessaire.

C'est brut, mais si vous êtes certain d'avoir au moins une, que vous pouvez faire:

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

Avoir DANS( 'tag1', 'tag2', 'tag1', 'tag1', 'tag1") seront facilement optimisé à l'écart par SQL Server. De Plus, vous obtenez l'indice direct cherche

J'aimerais passer un paramètre de type tableau (car il est SQL Server 2008), et de faire une where exists, ou jointure interne. Vous pouvez également utiliser le format XML, à l'aide de sp_xml_preparedocument, et même les index de la table temporaire.

De manière correcte, à mon humble avis, est de stocker la liste dans une chaîne de caractères (une longueur limitée par le fait que le support de SGBD); le seul truc, c'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 transformer

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

dans un

in (select ...)

ou (la solution je serais probablement préférez) régulièrement joindre, si vous venez d'ajouter un "distinctes" pour éviter les problèmes avec des valeurs en double dans la liste.

Malheureusement, les techniques pour découper une chaîne de caractères sont assez spécifiques à un produit.
Ici est la version de 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;

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 de 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 le nombre de lignes que le nombre maximal de
les éléments que nous pouvons trouver dans la liste)

Je pense que c'est un cas quand une statique de la requête n'est pas le chemin à parcourir. Générer dynamiquement la liste pour votre clause, échapper à votre guillemets simples, et de créer de façon dynamique SQL. Dans ce cas, vous probablement ne verrez 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 pour 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 que fait le personnage le plus de code, ou de la considérer comme une mauvaise pratique de créer de façon dynamique SQL.

J'ai vu la scission des fonctions de prendre plus de temps à l'exécution de la requête eux-mêmes dans de nombreux cas lorsque les paramètres deviennent grands. Une procédure stockée avec table de paramètres dans SQL 2008 est la seule autre option, que je considère comme bien que ce sera probablement plus lent dans votre cas. TVP sera probablement seulement être plus rapide pour les grandes listes si vous êtes à la recherche sur la clé primaire de la TABLE, parce que SQL va construire une table temporaire pour la liste de toute façon (si la liste est grande). Vous ne sais pas pour vous, sauf si vous l'essayez.

J'ai vu aussi des procédures stockées qui avait 500 paramètres avec des valeurs par défaut de la valeur null, et avoir OÙ Colonne1 DANS (@Param1, @Param2, @Param3, ..., @Param500). Cela a provoqué SQL pour créer une table temporaire, faire un tri/distinct, et ensuite faire une analyse de table au lieu d'une recherche d'index. C'est essentiellement ce que vous feriez le paramétrage de cette requête, bien que sur une assez petite échelle qu'il ne fera pas une différence notable. Je recommande vivement contre le fait d'avoir la valeur NULL dans votre DANS les listes, comme si cela est changé pour un PAS DANS il ne s'agira pas comme prévu. Vous pourriez générer dynamiquement la liste des paramètres, mais la seule chose évidente qui vous permettrait de gagner est que les objets d'échapper les guillemets simples pour vous. Cette approche est également légèrement plus lent sur le final de l'application depuis les objets à 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 appel sp_prepare, sp_execute autant de fois que vous exécutez la requête, suivi par sp_unprepare.

La réutilisation des plans d'exécution des procédures stockées ou des requêtes paramétrées peuvent vous donner un gain de performance, mais il fera de vous enfermer dans un plan d'exécution déterminé par la première requête est exécutée. Que peut-être pas l'idéal pour les requêtes suivantes, dans de nombreux cas. Dans votre cas, la réutilisation des plans d'exécution sera probablement un plus, mais il ne pourrait pas faire la différence à tous que l'exemple est vraiment une requête simple.

Falaises note:

Pour votre cas, tout ce que vous faites, que ce soit le paramétrage d'un nombre fixe d'éléments dans la liste (null si non utilisé), dynamique de la construction de la requête avec ou sans paramètres, ou à l'aide de procédures stockées avec table de paramètres 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 le test indique une meilleure performance.

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

SQL avec des paramètres dynamiques.

Requêtes avec de grandes listes:

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

J'avais à l'approche de ce par défaut avec le passage d'une fonction à valeur de table (qui retourne une table à partir d'une chaîne de caractères) à la mise EN condition.

Voici le code pour l'UDF (je l'ai eu de Débordement de Pile quelque part, je ne peux pas trouver la source de droit maintenant)

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 ce votre code devrait être aussi simple que cela:

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

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

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

Utiliser la procédure stockée suivante. Il utilise un custom fonction de répartition, 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

Dans ColdFusion nous venons de le faire:

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

Ici est une autre alternative. Il suffit de passer une liste délimitée par des virgules, comme une chaîne de caractère en paramètre à 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

J'ai une réponse qui ne nécessite pas un UDF, XML
Parce que DANS accepte une instruction select
ex: SELECT * from Test where Données (SÉLECTIONNER une Valeur à PARTIR de la TABLE)

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

Cela peut être fait avec une expression de table commune récursive, ou une requête avec un numéro de table (ou Master..spt_value)

Voici la CTE version.

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

Dans SQL Server 2016+ une autre possibilité est d'utiliser le OPENJSON fonction.

Cette approche est blogué sur dans OPENJSON - l'une des meilleures façons de sélectionner les lignes de la liste des 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 

Le seul coup gagnant n'est pas de jouer.

Pas infinie variabilité pour vous. Seulement fini variabilité.

Dans le SQL, vous disposez d'une clause de ce genre:

and ( {1}==0 or b.CompanyId in ({2},{3},{4},{5},{6}) )

Dans le code C# vous faire quelque chose comme ceci:

  int origCount = idList.Count;
if (origCount > 5) {
throw new Exception("You may only specify up to five originators to filter on.");
}
while (idList.Count < 5) { idList.Add(-1); }  // -1 is an impossible value
return ExecuteQuery<PublishDate>(getValuesInListSQL, 
origCount,   
idList[0], idList[1], idList[2], idList[3], idList[4]);

Donc, fondamentalement, si le nombre est 0 alors il n'y a pas de filtre et tout va de travers. Si le nombre est supérieur à 0 alors la valeur doit être dans la liste, mais la liste a été complétée à cinq avec impossible valeurs (de sorte que le SQL a encore un sens)

Parfois le boiteux solution est la seule qui fonctionne réellement.

    create FUNCTION [dbo].[ConvertStringToList]
(@str VARCHAR (MAX), @delimeter CHAR (1))
RETURNS 
@result TABLE (
[ID] INT NULL)
AS
BEG
IN
DECLARE @x XML 
SET @x = '<t>' + REPLACE(@str, @delimeter, '</t><t>') + '</t>'
INSERT INTO @result
SELECT DISTINCT x.i.value('.', 'int') AS token
FROM @x.nodes('//t') x(i)
ORDER BY 1
RETURN
END

--VOTRE REQUÊTE

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

Vous pouvez le faire dans une réutilisables façon, de la manière suivante -

public static class SqlWhereInParamBuilder
{
public static string BuildWhereInClause<t>(string partialClause, string paramPrefix, IEnumerable<t> parameters)
{
string[] parameterNames = parameters.Select(
(paramText, paramNumber) => "@" + paramPrefix + paramNumber.ToString())
.ToArray();
string inClause = string.Join(",", parameterNames);
string whereInClause = string.Format(partialClause.Trim(), inClause);
return whereInClause;
}
public static void AddParamsToCommand<t>(this SqlCommand cmd, string paramPrefix, IEnumerable<t> parameters)
{
string[] parameterValues = parameters.Select((paramText) => paramText.ToString()).ToArray();
string[] parameterNames = parameterValues.Select(
(paramText, paramNumber) => "@" + paramPrefix + paramNumber.ToString()
).ToArray();
for (int i = 0; i < parameterNames.Length; i++)
{
cmd.Parameters.AddWithValue(parameterNames[i], parameterValues[i]);
}
}
}

Pour plus de détails, jetez un oeil à ce blog - SQL paramétrée OÙ À l'alinéa c#

C'est une réutilisables variation de la solution en Marque de Support à une excellente réponse.

Méthode D'Extension:

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

Utilisation:

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

Créer une table temporaire où les noms sont stockés, puis utilisez la requête suivante:

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

Étape 1:-

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

Étape 2:-

@CurrentShipmentStatusIdArray [nvarchar](255) = NULL

Étape 3:-

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

ou

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