Procedura: Implementare la classe di supporto per utilizzare un SqlParameter con un elenco di valori in una clausola IN

Autore:

William Ryan MVP

DECLINAZIONE DI RESPONSABILITÀ COMUNITARIA SOLUZIONI CONTENUTO

MICROSOFT CORPORATION E/O I RELATIVI FORNITORI NON RENDONO RILASCIANO ALCUNA DICHIARAZIONE SULL'ACCURATEZZA DELLE INFORMAZIONI E LE IMMAGINI RELATIVE CONTENUTE NEL PRESENTE DOCUMENTO, L'AFFIDABILITÀ O DI IDONEITÀ. TUTTE QUESTE INFORMAZIONI E IMMAGINI RELATIVE SONO FORNITE "COSÌ COM'È" SENZA GARANZIE DI ALCUN TIPO. MICROSOFT E/O I RELATIVI FORNITORI NON RICONOSCONO ALCUNA GARANZIA O CONDIZIONE IN RELAZIONE A TALI INFORMAZIONI E IMMAGINI RELATIVE, INCLUSE TUTTE LE GARANZIE E CONDIZIONI DI COMMERCIABILITÀ, IDONEITÀ PER UNO SCOPO SPECIFICO, PERIZIA, TITOLO E NON VIOLAZIONE. L'UTENTE ACCETTA SPECIFICAMENTE CHE IN NESSUN CASO MICROSOFT E/O DEI SUOI FORNITORI SARÀ RESPONSABILI PER EVENTUALI DANNI DIRETTI, INDIRETTI, PUNITIVI, INCIDENTALI, SPECIALI, CONSEQUENZIALI O EVENTUALI DANNI DI QUALSIASI TIPO INCLUSI, SENZA LIMITAZIONE ALCUNA, DANNI PER PERDITA DI UTILIZZO, DATI O PROFITTI, DERIVANTI O IN QUALSIASI MODO CONNESSI CON L'UTILIZZO DI O DALL'INCAPACITÀ DI UTILIZZARE LE INFORMAZIONI E LE IMMAGINI RELATIVE CONTENUTE NEL PRESENTE DOCUMENTO , BASATO SU CONTRATTO, TORTO, NEGLIGENZA, RESPONSABILITÀ OGGETTIVA O IN CASO CONTRARIO, ANCHE SE MICROSOFT O I SUOI FORNITORI È STATA AVVERTITA DELLA POSSIBILITÀ DI DANNI.

SINTOMI

Si sta tentando di passare più valori separati da un delimitatore per un'istruzione SQL o Stored Procedure che utilizzerà l'elenco in un'istruzione IN e si passa come un unico elenco.

CAUSA

Ciò è causato da una funzionalità che rende utile in primo luogo utilizzare parametri - sono i valori inseriti nel loro di escape e passarli come un valore letterale.  Quindi se si imposta @SomeValue = 1,2,3,4,5 e viene utilizzato nella finestra Seleziona * da [TableName] dove [NomeColonna] IN @SomeValue, non si corrisponde a valori [NomeColonna] che sono state pari a 1,2,3,4 o 5.  Invece corrisponderebbe solo valori che sono stati esattamente "1,2,3,4,5".  Quindi, se questa è la nostra procedura:
<CODE>
CREATE PROCEDURE up_ProcedureHelper
@Tokens come VARCHAR (50) - o di qualsiasi lunghezza, è necessario
AS
Selezionare * da [TableName]
DOVE [NomeCampo] IN (@Tokens)
</CODE>
Questo codice avrà esito negativo:
<CODE>
stringa cs = ConfigurationSettings.AppSettings("ConnectString"));
utilizzo (SqlConnection cn = {SqlConnection(cs)) nuovo
SqlCommand cmd = nuovo SqlCommand ("up_ProcedureHelper", ConnectionName);
      cmd.CommandType = CommandType.StoredProcedure;
cmd. Parameters. Add ("@Token", "1,2,3,4,5);
CN. Open (); //In realtà Connection.Open ed ExecuteReader devono essere racchiuse Try/Catch/Finally
Dr = cmd. ExecuteReader(CommandBehavior.CloseConnection);
Debug. Assert (dr. HasRows, "Si deve avere righe ma non"); Ha esito negativo poiché ColumnName è un campo di tipo Integer e non contenga "1,2,3,4,5"

}</CODE>

RISOLUZIONE

Modificare la Stored Procedure per inserire una nuova metodologia
Un metodo semplice per risolvere questo problema consiste nel creare una tabella temporanea utilizzando funzionalità di tabella temporanea Sql Server 2000.  Utilizzeremo un semplice ciclo per analizzare la variabile che abbiamo passato separati da un delimitatore specificato.  In ogni passaggio del ciclo, inseriremo ogni variabile nella tabella temporanea.  Infine, verrà utilizzata una subquery per corrispondono ai valori nella clausola come :
<CODE>
CREATE PROCEDURE up_ProcedureHelper
@Tokens come VARCHAR (50) - o di qualsiasi lunghezza, è necessario
AS
DICHIARARE @Temp come varchar (50)
CREARE una tabella #HOLDER(TokenValues VARCHAR(50))
DURANTE il LEN(@Tokens) > 0
INIZIARE
Se CHARINDEX (',', @Tokens) = 0
INIZIARE
SET @TEMP = @Tokens
SET @Tokens = '
INSERIRE in VALUES(@Temp) #Holder (TokenValues)
FINE
ELSE
INIZIARE
SET @Temp = sinistra (@Tokens, CHARINDEX(',', @Tokens)-1)
INSERIRE in VALUES(@Temp) #Holder (TokenValues)
SET @Tokens = destra (@Tokens, LEN(@Tokens)-LEN(@Temp)-1)
FINE
FINE
Selezionare * da [TableName]
DOVE [NomeCampo] IN (SELECT TokenValues da #Holder)
</CODE>
 
Pertanto, se il parametro "1,2,3,4,5", avremo una tabella Temp, denominata rispettivamente #Holder con 5 righe e i valori 1,2,3,4 e 5.  In questa tabella sarà disponibili come viene stabilita la connessione creata in.  Non appena si chiude la tabella verrà rimossa automaticamente.  In circostanze normali, è assolutamente fondamentale ricordarsi di chiudere il.  Non si desidera qualsiasi correte qui e speriamo che la connessione viene chiuso, pertanto è bene utilizzare una durante le procedure di blocco e chiamare il metodo da tale posizione.   Se si utilizza C# , è possibile includere il blocco in un utilizzando in combinazione con il blocco di istruzione e assicurarsi che il è anche come (lo stesso principio si applica con tutti gli altri tipi di connessione).
<CODE>
stringa cs = ConfigurationSettings.AppSettings("ConnectString"));
utilizzo (SqlConnection cn = {SqlConnection(cs)) nuovo
SqlCommand cmd = nuovo SqlCommand ("up_ProcedureHelper", ConnectionName);
      cmd.CommandType = CommandType.StoredProcedure;
cmd. Parameters. Add ("@Token", "1,2,3,4,5");
CN. Open (); //In realtà Connection.Open ed ExecuteReader devono essere racchiuse Try/Catch/Finally
Dr = cmd. ExecuteReader(CommandBehavior.CloseConnection);
Debug. Assert (dr. HasRows, "Si deve avere righe ma non"); A questo punto l'asserzione ha esito positivo, e abbiamo 5 righe.
}
</CODE>

ULTERIORI INFORMAZIONI

una stored procedure in SQL Server
Per una trattazione e quando è una buona soluzione
Un'ottima spiegazione sul motivo per cui alle query
Livello di programmazione conversione di istruzioni SQL dinamiche in query
Proprietà

ID articolo: 555167 - Ultima revisione: 15 feb 2017 - Revisione: 1

Microsoft ADO.NET 1.1, Microsoft ADO.NET 1.0

Feedback