parameter - sql variable
Parametrizar una cláusula SQL IN (30)
¿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;
La respuesta aceptada funcionará , por supuesto, y es una de las formas a seguir, pero es anti-patrón.
La pregunta original tiene el requisitoE. 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)) + '',%'';
SQL Server 2008 . Debido a que esta pregunta a menudo se usa como duplicado, he agregado esta respuesta como referencia. En mi opinión, la mejor fuente para resolver este problema es lo que se ha publicado en este sitio:
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
Utilizar:
SELECT * FROM dbo.fnParseArray(''a,b,c,d,e,f'', '','')
CRÉDITOS PARA: Dinakar Nethi
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.
- Crear una tabla temporal con una columna.
-
INSERTcada valor de búsqueda en esa columna. - En lugar de usar una
IN, puede usar las reglas estándar deJOIN. (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.
Esto es grave, pero si tiene la garantía de tener al menos uno, podría hacer:
SELECT ...
...
WHERE tag IN( @tag1, ISNULL( @tag2, @tag1 ), ISNULL( @tag3, @tag1 ), etc. )
Teniendo IN (''tag1'', ''tag2'', ''tag1'', ''tag1'', ''tag1'') será fácilmente optimizado por SQL Server. Además, obtienes búsquedas directas en el índice.
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?
Me acercaría a esto de manera predeterminada al pasar una función con valor de tabla (que devuelve una tabla desde una cadena) a la condición IN.
Aquí está el código para el UDF (lo obtuve de en algún lugar, no puedo encontrar la fuente en este momento)
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
)
Una vez que obtengas esto, tu código sería tan simple como esto:
select * from Tags
where Name in (select s from dbo.split('';'',''ruby;rails;scruffy;rubyonrails''))
order by Count desc
A menos que tenga una cadena ridículamente larga, esto debería funcionar bien con el índice de la tabla.
Si es necesario, puede insertarlo en una tabla temporal, indexarla y luego ejecutar una combinación ...
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.
Puede pasar el parámetro como una cadena
Así que tienes la cuerda
DECLARE @tags
SET @tags = ‘ruby|rails|scruffy|rubyonrails’
select * from Tags
where Name in (SELECT item from fnSplit(@tags, ‘|’))
order by Count desc
Entonces todo lo que tienes que hacer es pasar la cadena como 1 parámetro.
Aquí está la función de división que uso.
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
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;
Si tiene SQL Server 2008 o posterior, usaría un parámetro de valor de tabla .
Si tiene la mala suerte de estar atascado en SQL Server 2005 , podría agregar una función CLR como esta,
[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 podrías usar de esta manera,
declare @desiredTags nvarchar(MAX);
set @desiredTags = ''ruby,rails,scruffy,rubyonrails'';
select * from Tags
where Name in [dbo].[Split] ('','', @desiredTags)
order by Count desc
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.
Para un número variable de argumentos como este, la única forma que conozco es generar el SQL explícitamente o hacer algo que implique rellenar una tabla temporal con los elementos que desea y unirse a la tabla temporal.
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 otra alternativa. Simplemente pase una lista delimitada por comas como un parámetro de cadena al procedimiento almacenado y:
CREATE PROCEDURE [dbo].[sp_myproc]
@UnitList varchar(MAX) = ''1,2,3''
AS
select column from table
where ph.UnitID in (select * from CsvToInt(@UnitList))
Y la función:
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
Aquí hay una publicación cruzada para una solución al mismo problema. Más robusto que los delimitadores reservados: incluye matrices de escape y anidadas, y comprende NULL y matrices vacías.
Cadena C # y T-SQL [] Funciones de utilidad de empaquetar / desempaquetar
A continuación, puede unirse a la función de valores de tabla.
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)
Creo que este es un caso en el que una consulta estática no es el camino a seguir. Genere dinámicamente la lista para su cláusula in, evite sus comillas simples y genere dinámicamente SQL. En este caso, probablemente no verá mucha diferencia con cualquier método debido a la pequeña lista, pero el método más eficiente es enviar el SQL exactamente como está escrito en su publicación. Creo que es un buen hábito escribirlo de la manera más eficiente, en lugar de hacer lo que hace al código más bonito, o considerar que es una mala práctica construir SQL de forma dinámica.
He visto que las funciones de división tardan más tiempo en ejecutarse que la consulta en muchos casos donde los parámetros aumentan de tamaño. Un procedimiento almacenado con parámetros de tabla de valores en SQL 2008 es la única otra opción que consideraría, aunque esto probablemente será más lento en su caso. Es probable que TVP solo sea más rápido para listas grandes si está buscando en la clave principal de TVP, porque de todos modos SQL creará una tabla temporal para la lista (si la lista es grande). No lo sabrás con seguridad a menos que lo pruebes.
También he visto procedimientos almacenados que tenían 500 parámetros con valores predeterminados de nulo, y que tienen WHERE Column1 IN (@ Param1, @ Param2, @ Param3, ..., @ Param500). Esto causó que SQL construya una tabla temporal, haga una ordenación / distinción y luego haga una exploración de tabla en lugar de una búsqueda de índice. Eso es esencialmente lo que estaría haciendo al parametrizar esa consulta, aunque en una escala lo suficientemente pequeña como para que no haga una diferencia notable. Recomiendo encarecidamente que no tenga NULL en sus listas IN, ya que si se cambia a NOT IN, no funcionará como debe. Podría construir dinámicamente la lista de parámetros, pero lo único obvio que obtendría es que los objetos escapen de las comillas simples para usted. Ese enfoque también es un poco más lento en el extremo de la aplicación, ya que los objetos tienen que analizar la consulta para encontrar los parámetros.Puede o no ser más rápido en SQL, ya que las consultas parametrizadas llaman a sp_prepare, sp_execute tantas veces como se ejecuta la consulta, seguido de sp_unprepare.
La reutilización de los planes de ejecución para procedimientos almacenados o consultas parametrizadas puede proporcionarle una ganancia de rendimiento, pero lo bloqueará en un plan de ejecución determinado por la primera consulta que se ejecuta. Eso puede ser menos que ideal para consultas posteriores en muchos casos. En su caso, la reutilización de los planes de ejecución probablemente será una ventaja, pero puede que no haga ninguna diferencia, ya que el ejemplo es una consulta muy simple.
Notas de los acantilados:
Para su caso, cualquier cosa que haga, ya sea la parametrización con un número fijo de elementos en la lista (nulo si no se usa), la creación dinámica de la consulta con o sin parámetros, o el uso de procedimientos almacenados con parámetros de valores de tabla no hará mucha diferencia . Sin embargo, mis recomendaciones generales son las siguientes:
Su caso / consultas sencillas con pocos parámetros:
SQL dinámico, tal vez con parámetros si las pruebas muestran un mejor rendimiento.
Consultas con planes de ejecución reutilizables, llamadas varias veces simplemente cambiando los parámetros o si la consulta es complicada:
SQL con parámetros dinámicos.
Consultas con grandes listas:
Procedimiento almacenado con parámetros de tabla valorados. Si la lista puede variar en una gran cantidad, use WITH RECOMPILE en el procedimiento almacenado, o simplemente use SQL dinámico sin parámetros para generar un nuevo plan de ejecución para cada consulta.
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
Otra solución posible es que, en lugar de pasar un número variable de argumentos a un procedimiento almacenado, pase una sola cadena que contenga los nombres que busca, pero hágalos únicos al rodearlos con ''<>''. Luego usa PATINDEX para encontrar los nombres:
SELECT *
FROM Tags
WHERE PATINDEX(''%<'' + Name + ''>%'',''<jo>,<john>,<scruffy>,<rubyonrails>'') > 0
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.
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);
Utilice el siguiente procedimiento almacenado. Utiliza una función de división personalizada, que se puede encontrar here .
create stored procedure GetSearchMachingTagNames
@PipeDelimitedTagNames varchar(max),
@delimiter char(1)
as
begin
select * from Tags
where Name in (select data from [dbo].[Split](@PipeDelimitedTagNames,@delimiter)
end