COMO: Implementar classe de programa auxiliar a ser utilizada uma SqlParameter com uma lista de valores de uma cláusula in

Autor:

William Ryan MVP

EXCLUSÃO DE RESPONSABILIDADE CONTEÚDO COMUNIDADE SOLUÇÕES

MICROSOFT CORPORATION E/OU OS RESPECTIVOS FORNECEDORES NÃO FAZEM NENHUMA AFIRMAÇÃO SOBRE A ADEQUAÇÃO, FIABILIDADE OU A EXACTIDÃO DAS INFORMAÇÕES E GRÁFICOS RELACIONADOS CONTIDOS NO PRESENTE REGULAMENTO. TODAS AS INFORMAÇÕES E GRÁFICOS RELACIONADOS SÃO FORNECIDOS "TAL COMO ESTÁ" SEM NENHUM TIPO DE GARANTIA. MICROSOFT E/OU OS RESPECTIVOS FORNECEDORES NÃO OFERECEM QUAISQUER GARANTIAS OU CONDIÇÕES NO QUE RESPEITA ÀS INFORMAÇÕES E GRÁFICOS RELACIONADOS, INCLUINDO TODAS AS GARANTIAS IMPLÍCITAS E CONDIÇÕES DE COMERCIALIZAÇÃO, ADEQUAÇÃO A UM FIM ESPECÍFICO, DILIGÊNCIA, TÍTULO E NÃO INFRACÇÃO. ESPECIFICAMENTE CONCORDA EM QUE NÃO MICROSOFT E/OU OS SEUS FORNECEDORES SERÁ RESPONSÁVEIS POR QUAISQUER PREJUÍZOS DIRECTOS, INDIRECTOS, PUNITIVOS, INCIDENTAIS, ESPECIAIS, CONSEQUENTES OU QUALQUER PREJUÍZO, INCLUINDO PREJUÍZOS, SEM LIMITAÇÃO, DANOS POR PERDA DE UTILIZAÇÃO, DADOS OU LUCROS, RESULTANTES OU EM QUALQUER FORMA RELACIONADOS COM A UTILIZAÇÃO DE OU IMPOSSIBILIDADE DE UTILIZAR AS INFORMAÇÕES E GRÁFICOS RELACIONADOS CONTIDOS NESTE DOCUMENTO , COM BASE NO CONTRATO, FACTO PREJUDICIAL, NEGLIGÊNCIA, RESPONSABILIDADE OBJECTIVA OU CASO CONTRÁRIO, MESMO MICROSOFT OU QUALQUER UM DOS SEUS FORNECEDORES TENHA SIDO AVISADA DA POSSIBILIDADE DE DANOS.

SINTOMAS

Está a tentar passar vários valores separados por um delimitador, a uma instrução de SQL ou procedimento armazenado que irá utilizar a lista numa instrução no e está a transmitir esta lista como um único.

CAUSA

Isto é realmente causado por uma funcionalidade do que faz com que os parâmetros benéfica utilizar em primeiro lugar - os valores no inserido-los de meios de evacuação e transmiti-los como um literal.  Assim se definir @SomeValue = 1,2,3,4,5 e utilizou no seleccionar * da [nomedatabela] onde [ColumnName] na @SomeValue, não teria correspondência valores [ColumnName] que foram iguais a 1,2,3,4 ou 5.  Em vez disso, seria apenas correspondem aos valores que foram exactamente "1,2,3,4,5".  Por isso, se este é o nosso procedimento:
<CODE>
CREATE PROCEDURE up_ProcedureHelper
@Tokens como VARCHAR (50) - ou qualquer outra terá de comprimento
AS
Seleccionar * da [nomedatabela]
Em que [nomecampo] em (@Tokens)
</CODE>
Este código irá falhar:
<CODE>
cadeia cs = ConfigurationSettings.AppSettings("ConnectString"));
utilizar (SqlConnection cn = {nova de SqlConnection(cs))
SqlCommand cmd = SqlCommand nova ("up_ProcedureHelper", NomeLigação);
      cmd.CommandType = CommandType.StoredProcedure;
cmd. Parameters.Add ("@Token", "1,2,3,4,5);
NC. Abrir (); //In realidade Connection.Open e ExecuteReader devem ser escritas tente/capturas/finalmente
Dr = cmd. ExecuteReader(CommandBehavior.CloseConnection);
Assert (dr. HasRows, "Estamos devem ter linhas mas não"); Falha porque o ColumnName é um campo inteiro e não conter "1,2,3,4,5"

}</CODE>

RESOLUÇÃO

Alterar o procedimento armazenado para acomodar a nova metodologia
Uma maneira fácil para resolver este problema é criar uma tabela temporária utilizando a funcionalidade de tabela temporária Sql Server 2000.  Utilizaremos um ciclo simple para analisar através a variável que tenha passado em que é separada por um determinado delimitador.  Em cada passagem do ciclo, a Microsoft vai inserir cada variável na tabela temporária.  Por fim, vamos utilizar uma subconsulta para fazer corresponder os valores na cláusula como :
<CODE>
CREATE PROCEDURE up_ProcedureHelper
@Tokens como VARCHAR (50) - ou qualquer outra terá de comprimento
AS
DECLARAR @Temp como VARCHAR(50)
Criar tabela #HOLDER(TokenValues VARCHAR(50))
ENQUANTO LEN(@Tokens) > 0
COMEÇAR
IF CHARINDEX (',', @Tokens) = 0
COMEÇAR
Definir @TEMP = @Tokens
Definir @Tokens = '
Inserir em VALUES(@Temp) #Holder (TokenValues)
FIM
ELSE
COMEÇAR
Definir @Temp = LEFT (@Tokens, CHARINDEX(',', @Tokens)-1)
Inserir em VALUES(@Temp) #Holder (TokenValues)
Definir @Tokens = RIGHT (@Tokens, LEN(@Tokens)-LEN(@Temp)-1)
FIM
FIM
Seleccionar * da [nomedatabela]
Em que [nomecampo] IN (SELECT TokenValues de #Holder)
</CODE>
 
Assim, partindo do princípio que o parâmetro "1,2,3,4,5", teremos uma tabela de Temp denominada #Holder com 5 linhas e os valores 1,2,3,4 & 5, respectivamente.  Esta tabela existirá, desde que a ligação que foi criada em está activo.  Assim fechar que a tabela será removida automaticamente.  Em circunstâncias normais é absolutamente indispensável que não se esqueça fechar o.  Que não pretende aqui qualquer correr e esperamos que a ligação é fechada, utilização assim um bloquear nos seus procedimentos e chamar o método a partir daí.   Se estiver a utilizar c# , pode moldar o bloco num utilizar instrução em conjunto com o bloco e certifique-se de que o é, bem como da (o mesmo princípio aplica-se com todos os outros tipos de ligação).
<CODE>
cadeia cs = ConfigurationSettings.AppSettings("ConnectString"));
utilizar (SqlConnection cn = {nova de SqlConnection(cs))
SqlCommand cmd = SqlCommand nova ("up_ProcedureHelper", NomeLigação);
      cmd.CommandType = CommandType.StoredProcedure;
cmd. Parameters.Add ("@Token", "1,2,3,4,5");
NC. Abrir (); //In realidade Connection.Open e ExecuteReader devem ser escritas tente/capturas/finalmente
Dr = cmd. ExecuteReader(CommandBehavior.CloseConnection);
Assert (dr. HasRows, "Estamos devem ter linhas mas não"); Agora é efectuada com êxito a asserção - e temos 5 linhas.
}
</CODE>

MAIS INFORMAÇÕES

um procedimento armazenado com parâmetros no SQL Server
Uma boa apresentação do e quando é uma boa solução
Uma boa apresentação na qual a razão para consultas
Como converter programaticamente instruções de SQL dinâmicas para consultas
Propriedades

ID do Artigo: 555167 - Última Revisão: 21/02/2017 - Revisão: 1

Microsoft ADO.NET 1.1, Microsoft ADO.NET 1.0

Comentários