with - Parametrizar una cláusula SQL IN




variables @@ sql server (20)

¿Cómo parametrizo una consulta que contiene una cláusula IN con un número variable de argumentos, como este?

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

En esta consulta, el número de argumentos podría estar entre 1 y 5.

Preferiría no usar un procedimiento almacenado dedicado para esto (o XML), pero si hay alguna manera elegante específica de SQL Server 2008 , estoy abierto a eso.


Aquí hay una técnica rápida y sucia que he usado:

SELECT * FROM Tags
WHERE '|ruby|rails|scruffy|rubyonrails|'
LIKE '%|' + Name + '|%'

Así que aquí está el código C #:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
const string cmdText = "select * from tags where '|' + @tags + '|' like '%|' + Name + '|%'";

using (SqlCommand cmd = new SqlCommand(cmdText)) {
   cmd.Parameters.AddWithValue("@tags", string.Join("|", tags);
}

Dos advertencias:

  • El rendimiento es terrible. LIKE "%...%" no están indexadas.
  • Asegúrate de no tener ninguna | , etiquetas en blanco o nulas o esto no funcionará

Hay otras formas de lograr esto que algunas personas pueden considerar más limpias, así que por favor siga leyendo.


En SQL Server 2016+ puede usar la función SPLIT_STRING :

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

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

o:

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

La respuesta aceptada funcionará , por supuesto, y es una de las formas a seguir, pero es anti-patrón.

E. Encontrar filas por lista de valores

Esto reemplaza al antipatrón común, como crear una cadena de SQL dinámico en la capa de aplicación o Transact-SQL, o mediante el uso del operador LIKE:

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

La pregunta original tiene el requisito SQL Server 2008 . Debido a que esta pregunta a menudo se usa como duplicado, he agregado esta respuesta como referencia.


Escuché a Jeff / Joel hablar de esto en el podcast de hoy ( episodio 34 , 2008-12-16 (MP3, 31 MB), 1 h 03 min 38 seg - 1 h 06 min 45 seg), y pensé que recordé el Desbordamiento de pila Estaba usando LINQ to SQL , pero tal vez fue abandonado. Esto es lo mismo en LINQ to SQL.

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

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

Eso es. Y, sí, LINQ ya mira hacia atrás lo suficiente, pero la cláusula Contains parece más al revés. Cuando tuve que hacer una consulta similar para un proyecto en el trabajo, naturalmente intenté hacerlo de la manera incorrecta haciendo una unión entre la matriz local y la tabla de SQL Server, calculando que el traductor de LINQ a SQL sería lo suficientemente inteligente como para manejar la traducción de alguna manera No lo hizo, pero proporcionó un mensaje de error que fue descriptivo y me indicó que usara Contains .

De todos modos, si ejecuta esto en el LINQPad altamente recomendado y ejecuta esta consulta, puede ver el SQL real que generó el proveedor de LINQ de SQL. Le mostrará cada uno de los valores parametrizados en una cláusula IN .


Esta es posiblemente una manera desagradable a medias, lo usé una vez, fue bastante efectivo.

Dependiendo de tus objetivos puede ser de utilidad.

  1. Crear una tabla temporal con una columna.
  2. INSERT cada valor de búsqueda en esa columna.
  3. En lugar de usar una IN , puede usar las reglas estándar de JOIN . (Flexibilidad ++)

Esto tiene un poco de flexibilidad adicional en lo que puede hacer, pero es más adecuado para situaciones en las que tiene una tabla grande para consultar, con una buena indexación y desea utilizar la lista parametrizada más de una vez. Ahorra tener que ejecutarlo dos veces y tener todo el saneamiento hecho manualmente.

Nunca llegué a perfilar exactamente qué tan rápido era, pero en mi situación era necesario.


La forma correcta de IMHO es almacenar la lista en una cadena de caracteres (limitada en longitud por lo que soporta el DBMS); el único truco es que (para simplificar el procesamiento) tengo un separador (una coma en mi ejemplo) al principio y al final de la cadena. La idea es "normalizar sobre la marcha", convirtiendo la lista en una tabla de una columna que contiene una fila por valor. Esto te permite girar

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

en una

en (seleccionar ...)

o (la solución que probablemente preferiría) una combinación regular, si solo agrega una "distinta" para evitar problemas con los valores duplicados en la lista.

Desafortunadamente, las técnicas para cortar una cadena son bastante específicas del producto. Aquí está la versión 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;

La versión de 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, ',', ''))));

y la versión 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, ',', ''));

(Por supuesto, "pivote" debe devolver tantas filas como el número máximo de elementos que podamos encontrar en la lista)


La pregunta original era "¿Cómo parametrizar una consulta ..."

Permítanme decir aquí, que esto no es una respuesta a la pregunta original. Ya hay algunas demostraciones de eso en otras buenas respuestas.

Dicho esto, avanza y marca esta respuesta, vota abajo, márcala como una respuesta ... haz lo que creas que es correcto.

Vea la respuesta de Mark Brackett para la respuesta preferida que I (y 231 personas más) votaron. El enfoque dado en su respuesta permite 1) el uso efectivo de las variables de enlace, y 2) para los predicados que son sustentables.

Respuesta seleccionada

Lo que quiero abordar aquí es el enfoque dado en la respuesta de Joel Spolsky, la respuesta "seleccionada" como la respuesta correcta.

El enfoque de Joel Spolsky es inteligente. Y funciona razonablemente, mostrará un comportamiento predecible y un rendimiento predecible, dados los valores "normales", y con los casos de borde normativo, como NULL y la cadena vacía. Y puede ser suficiente para una aplicación particular.

Pero en términos de generalizar este enfoque, también consideremos los casos de esquina más oscuros, como cuando la columna Name contiene un carácter comodín (como lo reconoce el predicado LIKE). El carácter comodín que veo más comúnmente es % (un signo de porcentaje). . Así que tratemos con eso aquí ahora, y luego pasemos a otros casos.

Algunos problemas con el carácter%

Considere un valor de Nombre de 'pe%ter' . (Para los ejemplos aquí, uso un valor de cadena literal en lugar del nombre de la columna). Una fila con un valor de Nombre de '' pe% ter 'sería devuelta por una consulta de la forma:

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

Pero esa misma fila no se devolverá si se invierte el orden de los términos de búsqueda:

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

El comportamiento que observamos es algo extraño. Cambiar el orden de los términos de búsqueda en la lista cambia el conjunto de resultados.

Casi no hace falta decir que es posible que no queramos que el pe%ter coincida con la mantequilla de maní, sin importar cuánto le guste.

Caso de la esquina oscura

(Sí, estaré de acuerdo en que este es un caso oscuro. Probablemente uno que probablemente no se probará. No esperaríamos un comodín en el valor de una columna. Podemos suponer que la aplicación impide que dicho valor se almacene. Pero en mi experiencia, rara vez he visto una restricción de base de datos que específicamente rechazaba caracteres o patrones que se considerarían comodines en el lado derecho de un operador de comparación LIKE .

Parcheando un agujero

Un enfoque para parchar este agujero es escapar del carácter comodín % . (Para cualquier persona que no esté familiarizada con la cláusula de escape del operador, aquí hay un enlace a la documentación de SQL Server .

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

Ahora podemos igualar el% literal. Por supuesto, cuando tengamos un nombre de columna, necesitaremos escapar dinámicamente del comodín. Podemos usar la función de REPLACE para encontrar ocurrencias del carácter % e insertar un carácter de barra invertida delante de cada uno, de esta manera:

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

Así que eso resuelve el problema con el comodín%. Casi.

Escapar de la fuga

Reconocemos que nuestra solución ha introducido otro problema. El personaje de escape. Vemos que también necesitaremos escapar de cualquier ocurrencia del propio personaje de escape. Esta vez, usamos el! como el personaje de escape:

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

El guión bajo también

Ahora que estamos en un rollo, podemos agregar otro REPLACE manejar el comodín de subrayado. Y solo por diversión, esta vez, usaremos $ como el personaje de escape.

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

Prefiero este enfoque a escapar porque funciona en Oracle y MySQL, así como en SQL Server. (Por lo general, uso \ \ la barra invertida como el carácter de escape, ya que ese es el carácter que usamos en las expresiones regulares.

Esos soportes molestos

SQL Server también permite que los caracteres comodín sean tratados como literales encerrándolos entre corchetes [] . Así que aún no hemos terminado de arreglar, al menos para SQL Server. Dado que los pares de paréntesis tienen un significado especial, también tendremos que escapar de ellos. Si logramos escapar de los soportes correctamente, al menos no tendremos que molestarnos con el guión y el quilate dentro de los soportes. Y podemos dejar cualquier carácter de % y _ dentro de los corchetes escapados, ya que básicamente habremos desactivado el significado especial de los corchetes.

Encontrar pares de paréntesis no debería ser tan difícil. Es un poco más difícil que manejar las ocurrencias de singleton% y _. (Tenga en cuenta que no es suficiente simplemente escapar de todas las apariciones de corchetes, ya que se considera que un corchete Singleton es un literal, y no es necesario que se escape. La lógica se está poniendo un poco más borrosa de lo que puedo manejar sin ejecutar más casos de prueba .)

La expresión en línea se ensucia

Esa expresión en línea en el SQL es cada vez más larga y fea. Probablemente podamos hacer que funcione, pero el cielo ayuda al pobre alma que viene detrás y tiene que descifrarlo. Como fanático de las expresiones en línea, me inclino por no usar uno aquí, principalmente porque no quiero dejar un comentario que explique la razón del desorden y disculparme por ello.

Una función donde?

Bien, entonces, si no manejamos eso como una expresión en línea en el SQL, la alternativa más cercana que tenemos es una función definida por el usuario. Y sabemos que eso no acelerará las cosas (a menos que podamos definir un índice en él, como podríamos hacerlo con Oracle). Si tenemos que crear una función, es mejor que hagamos eso en el código que llama al SQL. declaración.

Y esa función puede tener algunas diferencias en el comportamiento, dependiendo del DBMS y la versión. (Un agradecimiento a todos los desarrolladores de Java que estén tan interesados ​​en poder utilizar indistintamente cualquier motor de base de datos).

Conocimiento del dominio

Es posible que tengamos conocimiento especializado del dominio para la columna (es decir, el conjunto de valores permitidos aplicados para la columna. Podemos saber a priori que los valores almacenados en la columna nunca contendrán un signo de porcentaje, un guión bajo o un corchete). En ese caso, solo incluimos un comentario rápido de que esos casos están cubiertos.

Los valores almacenados en la columna pueden permitir% o _ caracteres, pero una restricción puede requerir que se escapen esos valores, tal vez utilizando un carácter definido, de tal manera que los valores sean LIKE comparación "segura". Nuevamente, un rápido comentario sobre el conjunto de valores permitido, y en particular qué personaje se usa como un personaje de escape, y sigue el enfoque de Joel Spolsky.

Pero, sin el conocimiento especializado y una garantía, es importante para nosotros al menos considerar el manejo de esos casos oscuros de esquina, y considerar si el comportamiento es razonable y "según la especificación".

Otros temas recapitulados.

Creo que otros ya han señalado suficientemente algunas de las otras áreas de preocupación comúnmente consideradas:

  • Inyección SQL (tomando lo que parece ser información suministrada por el usuario, e incluirla en el texto SQL en lugar de proporcionarla a través de variables de vinculación. No es necesario utilizar variables de vinculación, es solo un método conveniente para frustrar la inyección de SQL. maneras de lidiar con eso:

  • plan de optimizador que utiliza el escaneo de índice en lugar de las búsquedas de índice, posible necesidad de una expresión o función para escapar comodines (posible índice de expresión o función)

  • el uso de valores literales en lugar de las variables de enlace impacta la escalabilidad

Conclusión

Me gusta el enfoque de Joel Spolsky. Es inteligente Y funciona.

Pero tan pronto como lo vi, inmediatamente vi un problema potencial con él, y no es mi naturaleza dejarlo pasar. No quiero ser crítico con los esfuerzos de otros. Sé que muchos desarrolladores toman su trabajo muy personalmente, porque invierten mucho en él y se preocupan mucho por él. Así que por favor entiendan, esto no es un ataque personal. Lo que estoy identificando aquí es el tipo de problema que surge en la producción en lugar de las pruebas.

Sí, he ido muy lejos de la pregunta original. Pero, ¿dónde más puedo dejar esta nota con respecto a lo que considero un problema importante con la respuesta "seleccionada" para una pregunta?


Para SQL Server 2008, puede utilizar un parámetro con valores de tabla . Es un poco de trabajo, pero podría decirse que es más limpio que mi otro método .

Primero, tienes que crear un tipo.

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

Entonces, su código ADO.NET se ve así:

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

Pasaría un parámetro de tipo de tabla (ya que es SQL Server 2008 ), y haría un where exists , o interno. También puede usar XML, usar sp_xml_preparedocument , e incluso indexar esa tabla temporal.


Puedes parametrizar cada valor, así que algo como:

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

Lo que te dará:

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"

No, esto no está abierto a la inyección de SQL . El único texto inyectado en CommandText no se basa en las entradas del usuario. Se basa únicamente en el prefijo "@tag" codificado y en el índice de una matriz. El índice siempre será un número entero, no es generado por el usuario y es seguro.

Los valores ingresados ​​por el usuario todavía están rellenos de parámetros, por lo que no hay ninguna vulnerabilidad allí.

Editar:

Dejando a un lado la inyección, tenga en cuenta que la construcción del texto del comando para acomodar un número variable de parámetros (como anteriormente) impide la capacidad del servidor SQL para aprovechar las consultas almacenadas en caché. El resultado neto es que casi seguro que pierde el valor de usar parámetros en primer lugar (en lugar de simplemente insertar las cadenas de predicado en el propio SQL).

No es que los planes de consulta en caché no sean valiosos, pero IMO esta consulta no es lo suficientemente complicada como para ver mucho beneficio de ella. Si bien los costos de compilación pueden acercarse (o incluso exceder) a los costos de ejecución, aún está hablando milisegundos.

Si tiene suficiente RAM, esperaría que SQL Server probablemente también almacene en caché un plan para los conteos comunes de parámetros. Supongo que siempre se podrían agregar cinco parámetros, y dejar que las etiquetas no especificadas sean NULAS. El plan de consulta debería ser el mismo, pero me parece bastante feo y no estoy seguro de que valga la pena la micro optimización (aunque, en - puede que valga la pena).

Además, SQL Server 7 y versiones posteriores parametrizarán automáticamente las consultas , por lo que el uso de parámetros no es realmente necesario desde el punto de vista del rendimiento; sin embargo, es crítico desde el punto de vista de la seguridad, especialmente con datos ingresados ​​por el usuario como este.


Si llama desde .NET, puede usar 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});

Aquí Dapper piensa, para que no tengas que hacerlo. Algo similar es posible con LINQ to SQL , por supuesto:

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;

Tenemos una función que crea una variable de tabla a la que puede unirse:

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 

Asi que:

@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

Aquí hay otra respuesta a este problema.

(nueva versión publicada el 4/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;
    }

Aclamaciones.


Puede ser que podamos usar XML aquí:

    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)

Tengo una respuesta que no requiere un UDF, XML porque IN acepta una declaración de selección, por ejemplo, SELECT * FROM Test donde Data IN (SELECT Value FROM TABLE)

Realmente solo necesitas una forma de convertir la cadena en una tabla.

Esto se puede hacer con un CTE recursivo o una consulta con una tabla numérica (o Master..spt_value)

Aquí está la versión 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);


Aquí hay una técnica que recrea una tabla local para ser usada en una cadena de consulta. Hacerlo de esta manera elimina todos los problemas de análisis.

La cadena se puede construir en cualquier idioma. En este ejemplo, utilicé SQL, ya que ese era el problema original que intentaba resolver. Necesitaba una forma limpia de pasar los datos de la tabla sobre la marcha en una cadena que se ejecutaría más tarde.

Usar un tipo definido por el usuario es opcional. La creación del tipo solo se crea una vez y se puede hacer antes de tiempo. De lo contrario, simplemente agregue un tipo de tabla completa a la declaración en la cadena.

El patrón general es fácil de extender y se puede usar para pasar tablas más complejas.

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

En ColdFusion solo hacemos:

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

En SQL Server 2016+ otra posibilidad es usar la OPENJSONfunción.

Este enfoque se publicó en el blog de OPENJSON, una de las mejores formas de seleccionar filas por lista de ID .

Un ejemplo completo a continuación.

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 

Si tenemos cadenas almacenadas dentro de la cláusula IN con la coma (,) delimitada, podemos usar la función charindex para obtener los valores. Si usa .NET, entonces puede mapear con 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

Puede usar la declaración anterior en su código .NET y asignar el parámetro con SqlParameter.

Demo del violinista

EDITAR: cree la tabla llamada SelectedTags utilizando el siguiente script.

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

Uso una versión más concisa de la respuesta más votada :

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

Hace un ciclo a través de los parámetros de la etiqueta dos veces; pero eso no importa la mayor parte del tiempo (no será su cuello de botella; si lo es, desenrolle el bucle).

Si realmente estás interesado en el rendimiento y no quieres recorrer el bucle dos veces, aquí tienes una versión menos hermosa:

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






parameters