variable - Parametrizar uma cláusula SQL IN




table valued sql server (20)

Como parametrizar uma consulta contendo uma cláusula IN com um número variável de argumentos, como este?

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

Nesta consulta, o número de argumentos pode estar entre 1 e 5.

Eu preferiria não usar um procedimento armazenado dedicado para isso (ou XML), mas se houver alguma maneira elegante específica para o SQL Server 2008 , estou aberto a isso.


A maneira correta IMHO é armazenar a lista em uma seqüência de caracteres (limitada em comprimento por que o suporte do SGBD); o único truque é que (para simplificar o processamento) eu tenho um separador (uma vírgula no meu exemplo) no início e no final da string. A idéia é "normalizar em tempo real", transformando a lista em uma tabela de uma coluna que contém uma linha por valor. Isso permite que você ligue

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

em um

em (selecione ...)

ou (a solução que eu provavelmente preferiria) uma junção regular, se você simplesmente adicionar um "distinto" para evitar problemas com valores duplicados na lista.

Infelizmente, as técnicas para cortar uma cadeia são bastante específicas do produto. Aqui está a versão do 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;

A versão do 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, ',', ''))));

e a versão do 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, ',', ''));

(É claro que "pivot" deve retornar tantas linhas quanto o número máximo de itens que podemos encontrar na lista)


A pergunta original era "Como parametrizar uma consulta ..."

Deixe-me afirmar aqui, que isso não é uma resposta à pergunta original. Já existem algumas demonstrações disso em outras boas respostas.

Com isto dito, vá em frente e marque esta resposta, downvote-a, marque-a como não uma resposta ... faça o que você acredita que está certo.

Veja a resposta de Mark Brackett para a resposta preferida que eu (e outros 231) defendi. A abordagem dada em sua resposta permite 1) para o uso efetivo de variáveis ​​vinculadas, e 2) para predicados que são sargáveis.

Resposta selecionada

O que eu quero abordar aqui é a abordagem dada na resposta de Joel Spolsky, a resposta "selecionada" como a resposta certa.

A abordagem de Joel Spolsky é inteligente. E funciona razoavelmente, exibirá comportamento previsível e desempenho previsível, dados valores "normais" e com os casos de borda normativos, como NULL e a string vazia. E isso pode ser suficiente para uma aplicação específica.

Mas em termos generalizando essa abordagem, vamos considerar também os casos de canto mais obscuros, como quando a coluna Name contém um caractere curinga (conforme reconhecido pelo predicado LIKE). O caractere curinga que eu vejo mais comumente usado é % (um sinal de porcentagem). . Então, vamos lidar com isso aqui agora e depois passar para outros casos.

Alguns problemas com% character

Considere um valor de nome de 'pe%ter' . (Para os exemplos aqui, eu uso um valor de string literal no lugar do nome da coluna.) Uma linha com um valor de nome de 'pe% ter' seria retornada por uma consulta do formulário:

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

Mas essa mesma linha não será retornada se a ordem dos termos de pesquisa for revertida:

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

O comportamento que observamos é meio estranho. Alterar a ordem dos termos de pesquisa na lista altera o conjunto de resultados.

É quase desnecessário dizer que talvez não queiramos que os pe%ter tenham que combinar com a manteiga de amendoim, por mais que ele goste.

Caso de canto obscuro

(Sim, concordarei que este é um caso obscuro. Provavelmente um que provavelmente não será testado. Não esperamos um caractere curinga em um valor de coluna. Podemos supor que o aplicativo impede que esse valor seja armazenado. Mas Na minha experiência, eu raramente vi uma restrição de banco de dados que especificamente desaprovava caracteres ou padrões que seriam considerados curingas no lado direito de um operador de comparação LIKE .

Remendando um buraco

Uma abordagem para consertar este buraco é escapar do caractere curinga % . (Para quem não está familiarizado com a cláusula de escape no operador, aqui está um link para a documentação do SQL Server .

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

Agora podemos combinar o literal%. É claro que, quando tivermos um nome de coluna, precisaremos escapar dinamicamente do curinga. Podemos usar a função REPLACE para encontrar ocorrências do caractere % e inserir um caractere de barra invertida na frente de cada um, assim:

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

Então, isso resolve o problema com o curinga%. Quase.

Escapar da fuga

Reconhecemos que nossa solução introduziu outro problema. O caractere de escape. Nós vemos que também precisaremos escapar de quaisquer ocorrências do próprio caractere de escape. Desta vez, usamos o! como o caractere de escape:

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

O sublinhado também

Agora que estamos bem, podemos adicionar outro REPLACE tratar o caractere curinga de sublinhado. E só por diversão, desta vez, vamos usar $ como o personagem de escape.

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

Eu prefiro essa abordagem para escapar porque funciona em Oracle e MySQL, bem como o SQL Server. (Eu costumo usar o \ backslash como o caractere de escape, já que esse é o caractere que usamos em expressões regulares. Mas por que ser restringido por convenção!

Aqueles parênteses irritantes

O SQL Server também permite que caracteres curinga sejam tratados como literais, colocando-os entre colchetes [] . Portanto, ainda não terminamos de corrigir, pelo menos para o SQL Server. Como os pares de colchetes têm um significado especial, também precisamos escapar deles. Se conseguirmos escapar dos colchetes adequadamente, pelo menos não precisaremos nos preocupar com o hífen - e com o quilate - entre os colchetes. E podemos deixar qualquer caractere % e _ entre os colchetes que escaparam, já que basicamente desativamos o significado especial dos colchetes.

Encontrar pares de colchetes não deve ser tão difícil. É um pouco mais difícil do que manipular as ocorrências de singleton% e _. (Observe que não é suficiente apenas escapar de todas as ocorrências de colchetes, porque um colchete singleton é considerado literal e não precisa ser escapado. A lógica está ficando um pouco mais imprecisa do que eu posso manipular sem executar mais casos de teste .)

Expressão inline fica confusa

Essa expressão inline no SQL está ficando mais longa e feia. Nós provavelmente podemos fazer isso funcionar, mas o céu ajuda a pobre alma que vem para trás e tem que decifrá-lo. Tanto quanto sou fã por expressões inline, estou inclinado a não usar um aqui, principalmente porque não quero ter que deixar um comentário explicando o motivo da bagunça e me desculpando por isso.

Uma função onde?

Ok, então, se não lidarmos com isso como uma expressão inline no SQL, a alternativa mais próxima que temos é uma função definida pelo usuário. E sabemos que isso não acelera nada (a menos que possamos definir um índice sobre ele, como poderíamos fazer com o Oracle.) Se tivermos que criar uma função, talvez seja melhor fazer isso no código que chama o SQL. declaração.

E essa função pode ter algumas diferenças de comportamento, dependendo do SGBD e da versão. (Um grito para todos vocês, desenvolvedores Java, estão tão interessados ​​em poder usar qualquer mecanismo de banco de dados de forma intercambiável.)

Conhecimento de domínio

Podemos ter conhecimento especializado do domínio para a coluna (ou seja, o conjunto de valores permitidos impostos para a coluna. Podemos saber a priori que os valores armazenados na coluna nunca conterão um sinal de porcentagem, um sublinhado ou um colchete Neste caso, apenas incluímos um comentário rápido de que esses casos estão cobertos.

Os valores armazenados na coluna podem permitir% ou _ caracteres, mas uma restrição pode exigir que esses valores sejam escapados, talvez usando um caractere definido, de tal forma que os valores sejam "LIKE" como "seguros". Novamente, um rápido comentário sobre o conjunto permitido de valores, e em particular qual personagem é usado como um caractere de escape, e segue a abordagem de Joel Spolsky.

Mas, sem o conhecimento especializado e a garantia, é importante que consideremos, pelo menos, o tratamento desses casos obscuros e consideremos se o comportamento é razoável e "de acordo com a especificação".

Outras questões recapituladas

Acredito que outros já apontaram suficientemente algumas das outras áreas consideradas comuns:

  • Injeção de SQL (pegando o que pareceria ser informação fornecida pelo usuário, e incluindo isso no texto SQL ao invés de fornecê-los através de variáveis ​​de ligação. Usando variáveis ​​de ligação não é necessário, é apenas uma abordagem conveniente para impedir a injeção de SQL. maneiras de lidar com isso:

  • plano otimizador usando varredura de índice em vez de busca de índice, possível necessidade de uma expressão ou função para curing wildcards (possível índice de expressão ou função)

  • usando valores literais no lugar de variáveis ​​de ligação afeta a escalabilidade

Conclusão

Eu gosto da abordagem de Joel Spolsky. É inteligente. E isso funciona.

Mas assim que eu vi, eu imediatamente vi um problema em potencial com isso, e não é da minha natureza deixá-lo deslizar. Não quero criticar os esforços dos outros. Eu sei que muitos desenvolvedores levam muito a sério seu trabalho, porque investem muito nisso e se importam muito com isso. Então, por favor, entenda, isso não é um ataque pessoal. O que estou identificando aqui é o tipo de problema que surge na produção em vez de ser testado.

Sim, eu fui longe da questão original. Mas onde mais deixar esta nota sobre o que eu considero ser uma questão importante com a resposta "selecionada" para uma pergunta?


Esta é possivelmente uma maneira meio desagradável de fazer isso, eu usei uma vez, foi bastante eficaz.

Dependendo de seus objetivos, pode ser útil.

  1. Crie uma tabela temporária com uma coluna.
  2. INSERT cada valor de pesquisa nessa coluna.
  3. Em vez de usar um IN , você pode usar as regras padrão do JOIN . (Flexibilidade ++)

Isso tem um pouco de flexibilidade adicional no que você pode fazer, mas é mais adequado para situações em que você tem uma tabela grande para consultar, com boa indexação e deseja usar a lista parametrizada mais de uma vez. Salva ter que executá-lo duas vezes e ter todo o saneamento feito manualmente.

Eu nunca cheguei a analisar exatamente o quão rápido foi, mas na minha situação era necessário.


Eu ouvi Jeff / Joel falar sobre isso no podcast hoje ( episódio 34 , 2008-12-16 (MP3, 31 MB), 1 h 03 min 38 segs - 1 h 06 min 45 segs), e eu achei que me lembrei do estava usando o LINQ to SQL , mas talvez tenha sido descartado. Aqui está a mesma coisa no LINQ to SQL.

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

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

É isso aí. E, sim, LINQ já olha para trás o suficiente, mas a cláusula Contains parece mais para trás para mim. Quando eu tive que fazer uma consulta semelhante para um projeto no trabalho, eu naturalmente tentei fazer isso da maneira errada, fazendo uma junção entre o array local e a tabela do SQL Server, imaginando que o conversor LINQ to SQL seria inteligente o suficiente para lidar com o tradução de alguma forma. Isso não aconteceu, mas forneceu uma mensagem de erro descritiva e me indicou o uso de Contains .

De qualquer forma, se você executar isso no altamente recomendado LINQPad e executar essa consulta, você pode exibir o SQL real que o provedor SQL LINQ gerou. Ele mostrará a você cada um dos valores sendo parametrizados em uma cláusula IN .


Isso é nojento, mas se você tiver certeza de ter pelo menos um, você poderia fazer:

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

Ter o IN ('tag1', 'tag2', 'tag1', 'tag1', 'tag1') será facilmente otimizado pelo SQL Server. Além disso, você obtém pesquisas de índice direto


Na minha opinião, a melhor fonte para resolver este problema, é o que foi publicado neste site:

Syscomments. Dinakar Nethi

CREATE FUNCTION dbo.fnParseArray (@Array VARCHAR(1000),@separator CHAR(1))
RETURNS @T Table (col1 varchar(50))
AS 
BEGIN
 --DECLARE @T Table (col1 varchar(50))  
 -- @Array is the array we wish to parse
 -- @Separator is the separator charactor such as a comma
 DECLARE @separator_position INT -- This is used to locate each separator character
 DECLARE @array_value VARCHAR(1000) -- this holds each array value as it is returned
 -- For my loop to work I need an extra separator at the end. I always look to the
 -- left of the separator character for each array value

 SET @array = @array + @separator

 -- Loop through the string searching for separtor characters
 WHILE PATINDEX('%' + @separator + '%', @array) <> 0 
 BEGIN
    -- patindex matches the a pattern against a string
    SELECT @separator_position = PATINDEX('%' + @separator + '%',@array)
    SELECT @array_value = LEFT(@array, @separator_position - 1)
    -- This is where you process the values passed.
    INSERT into @T VALUES (@array_value)    
    -- Replace this select statement with your processing
    -- @array_value holds the value of this element of the array
    -- This replaces what we just processed with and empty string
    SELECT @array = STUFF(@array, 1, @separator_position, '')
 END
 RETURN 
END

Usar:

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

CRÉDITOS PARA: Dinakar Nethi


Para o SQL Server 2008, você pode usar um parâmetro com valor de tabela . É um pouco de trabalho, mas é indiscutivelmente mais limpo do que o meu outro método .

Primeiro, você tem que criar um tipo

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

Então, seu código ADO.NET se parece com isto:

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

Se você está chamando do .net, você poderia usar o 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});

Aqui Dapper faz o pensamento, então você não precisa. Algo semelhante é possível com o LINQ to SQL , é claro:

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;

Temos a função que cria uma variável de tabela que você pode unir para:

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 

Assim:

@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

Você pode parametrizar cada valor, então 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]);
    }
}

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"

Não, isso não está aberto para injeção de SQL . O único texto injetado no CommandText não é baseado na entrada do usuário. Baseia-se exclusivamente no prefixo "@tag" codificado e no índice de um array. O índice sempre será um inteiro, não é gerado pelo usuário e é seguro.

Os valores inseridos pelo usuário ainda são preenchidos em parâmetros, portanto, não há vulnerabilidade lá.

Editar:

Injeção de preocupações à parte, tome cuidado ao observar que a construção do texto de comando para acomodar um número variável de parâmetros (como acima) impede a capacidade do SQL Server de aproveitar as consultas armazenadas em cache. O resultado final é que você quase certamente perderá o valor de usar parâmetros em primeiro lugar (em vez de simplesmente inserir as cadeias de predicados no próprio SQL).

Não que os planos de consulta em cache não sejam valiosos, mas a IMO, essa consulta, não é nem de perto complicada o suficiente para ver muitos benefícios dela. Enquanto os custos de compilação podem se aproximar (ou mesmo exceder) dos custos de execução, você ainda está falando milissegundos.

Se você tiver RAM suficiente, esperaria que o SQL Server provavelmente armazenasse em cache um plano para as contagens comuns de parâmetros também. Eu suponho que você poderia sempre adicionar cinco parâmetros, e deixar as tags não especificadas serem NULL - o plano de consulta deve ser o mesmo, mas parece muito feio para mim e não tenho certeza se valeria a micro-otimização (embora, no - pode muito bem valer a pena).

Além disso, o SQL Server 7 e posteriores parametrizam as consultas automaticamente , portanto, usar parâmetros não é realmente necessário do ponto de vista do desempenho - no entanto, é crítico do ponto de vista da segurança - especialmente com dados inseridos pelo usuário como este.


Aqui está outra resposta para esse problema.

(nova versão publicada em 6/4/13).

    private static DataSet GetDataSet(SqlConnectionStringBuilder scsb, string strSql, params object[] pars)
    {
        var ds = new DataSet();
        using (var sqlConn = new SqlConnection(scsb.ConnectionString))
        {
            var sqlParameters = new List<SqlParameter>();
            var replacementStrings = new Dictionary<string, string>();
            if (pars != null)
            {
                for (int i = 0; i < pars.Length; i++)
                {
                    if (pars[i] is IEnumerable<object>)
                    {
                        List<object> enumerable = (pars[i] as IEnumerable<object>).ToList();
                        replacementStrings.Add("@" + i, String.Join(",", enumerable.Select((value, pos) => String.Format("@_{0}_{1}", i, pos))));
                        sqlParameters.AddRange(enumerable.Select((value, pos) => new SqlParameter(String.Format("@_{0}_{1}", i, pos), value ?? DBNull.Value)).ToArray());
                    }
                    else
                    {
                        sqlParameters.Add(new SqlParameter(String.Format("@{0}", i), pars[i] ?? DBNull.Value));
                    }
                }
            }
            strSql = replacementStrings.Aggregate(strSql, (current, replacementString) => current.Replace(replacementString.Key, replacementString.Value));
            using (var sqlCommand = new SqlCommand(strSql, sqlConn))
            {
                if (pars != null)
                {
                    sqlCommand.Parameters.AddRange(sqlParameters.ToArray());
                }
                else
                {
                    //Fail-safe, just in case a user intends to pass a single null parameter
                    sqlCommand.Parameters.Add(new SqlParameter("@0", DBNull.Value));
                }
                using (var sqlDataAdapter = new SqlDataAdapter(sqlCommand))
                {
                    sqlDataAdapter.Fill(ds);
                }
            }
        }
        return ds;
    }

Felicidades.


Eu abordaria isso por padrão ao passar uma função com valor de tabela (que retorna uma tabela de uma string) para a condição IN.

Aqui está o código para o UDF (eu peguei do em algum lugar, não consigo encontrar a fonte agora)

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
  )

Uma vez que você conseguiu isso, seu código seria tão simples quanto isto:

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

A menos que você tenha uma string ridiculamente longa, isso deve funcionar bem com o índice da tabela.

Se necessário, você pode inseri-lo em uma tabela temporária, indexá-lo e, em seguida, executar uma junção ...


Para um número variável de argumentos como este, a única maneira que conheço é gerar o SQL explicitamente ou fazer algo que envolva o preenchimento de uma tabela temporária com os itens desejados e a união à tabela temporária.


Pode ser que possamos usar XML aqui:

    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)

Aqui está um cross-post para uma solução para o mesmo problema. Mais robusto que os delimitadores reservados - inclui matrizes de escape e aninhadas e compreende NULLs e matrizes vazias.

C # & T-SQL string [] Funções do utilitário Pack / Unpack

Você pode juntar-se à função com valor de tabela.


Aqui está uma técnica que recria uma tabela local para ser usada em uma string de consulta. Fazê-lo desta maneira elimina todos os problemas de análise.

A string pode ser construída em qualquer idioma. Neste exemplo, usei o SQL, já que esse era o problema original que eu estava tentando resolver. Eu precisava de uma maneira limpa de passar os dados da tabela rapidamente em uma string para ser executada mais tarde.

Usar um tipo definido pelo usuário é opcional. Criar o tipo é criado apenas uma vez e pode ser feito antes do tempo. Caso contrário, basta adicionar um tipo de tabela completo à declaração na string.

O padrão geral é fácil de estender e pode ser usado para passar tabelas mais complexas.

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

Eu uso uma versão mais concisa da resposta mais 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)));

Ele percorre os parâmetros da tag duas vezes; mas isso não importa a maior parte do tempo (não será o seu gargalo; se for, desenrole o loop).

Se você está realmente interessado em performance e não quer repetir o loop duas vezes, aqui está uma versão menos bonita:

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

No ColdFusion nós apenas fazemos:

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

Outra solução possível é, em vez de passar um número variável de argumentos para um procedimento armazenado, passar uma única string contendo os nomes que você procura, mas torná-los únicos, envolvendo-os com '<>'. Em seguida, use PATINDEX para encontrar os nomes:

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

Se tivermos strings armazenadas dentro da cláusula IN com a vírgula (,) delimitada, podemos usar a função charindex para obter os valores. Se você usar o .NET, poderá mapear com 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

Você pode usar a instrução acima em seu código .NET e mapear o parâmetro com SqlParameter.

Demo do Fiddler

EDIT: Crie a tabela chamada SelectedTags usando o seguinte 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




parameters