Cómo: Implementar la clase de aplicación auxiliar para utilizar un SqlParameter con una lista de valores en una cláusula IN

Autor:

William Ryan MVP

COMUNIDAD SOLUCIONES CONTENIDO RENUNCIA

MICROSOFT CORPORATION Y/O SUS RESPECTIVOS PROVEEDORES GARANTIZAN LA IDONEIDAD, FIABILIDAD O EXACTITUD DE LA INFORMACIÓN Y LOS GRÁFICOS RELACIONADOS CONTENIDOS EN ESTE DOCUMENTO. DICHA INFORMACIÓN Y LOS GRÁFICOS RELACIONADOS SE PROPORCIONAN "TAL CUAL" SIN GARANTÍA DE NINGÚN TIPO. MICROSOFT Y/O SUS RESPECTIVOS PROVEEDORES RENUNCIAN A TODA GARANTÍA Y CONDICIÓN RESPECTO A ESTA INFORMACIÓN Y GRÁFICOS RELACIONADOS, INCLUIDAS TODAS LAS GARANTÍAS IMPLÍCITAS Y LAS CONDICIONES DE COMERCIABILIDAD, IDONEIDAD PARA UN FIN DETERMINADO, ESFUERZO RAZONABLE, TÍTULO Y AUSENCIA DE INFRACCIÓN. USTED ACEPTA ESPECÍFICAMENTE QUE EN NINGÚN CASO MICROSOFT O SUS PROVEEDORES SERÁN RESPONSABLES DE DAÑOS DIRECTOS, INDIRECTOS, PUNITIVOS, INCIDENTALES, ESPECIALES, CONSECUENTES NI NINGÚN DAÑO, INCLUIDOS, SIN LIMITACIÓN, DAÑOS POR PÉRDIDA DE USO, DATOS O BENEFICIOS, QUE SURJA DE O EN CUALQUIER FORMA RELACIONADA CON EL USO DE O INCAPACIDAD DE USO DE LA INFORMACIÓN Y LOS GRÁFICOS RELACIONADOS CONTENIDOS EN ESTE DOCUMENTO , YA SEA BASADO EN CONTRATO, AGRAVIO, NEGLIGENCIA, RESPONSABILIDAD ESTRICTA O DE LO CONTRARIO, INCLUSO SI TIENE MICROSOFT O CUALQUIERA DE SUS PROVEEDORES AVISADO DE LA POSIBILIDAD DE DAÑOS.

SÍNTOMAS

Está intentando pasar varios valores separados por un delimitador a una instrucción SQL o procedimiento almacenado que utilizará la lista en una instrucción de y se pasa en esta lista como una sola.

CAUSA

Realmente la causa es una característica que hace beneficioso utilizar en primer lugar los parámetros - escapar los valores insertados en a ellos y pasarlos como un literal.  Por lo que si establece @SomeValue = 1,2,3,4,5 y lo usó en SELECT * FROM [NombreTabla] donde [nombreColumna] en @SomeValue, no coincidiría con los valores [nombreColumna] eran iguales a 1,2,3,4 o 5.  En su lugar solo coincidiría con valores que estaban exactamente "1,2,3,4,5".  Por tanto, si se trata de nuestro procedimiento:
<CODE>
CREATE PROCEDURE up_ProcedureHelper
@Tokens como VARCHAR (50), o cualquier otra longitud deberá
AS
Seleccionar * desde [TableName]
DONDE [NombreDelCampo] en (@Tokens)
</CODE>
Se producirá un error en este código:
<CODE>
cadena cs = ConfigurationSettings.AppSettings("ConnectString"));
uso (SqlConnection cn = new {SqlConnection(cs))
SqlCommand cmd = new SqlCommand ("up_ProcedureHelper", ConnectionName);
      cmd.CommandType = CommandType.StoredProcedure;
cmd. Parameters.Add ("@Token", "1,2,3,4,5);
CN. () Abierto; //In realidad Connection.Open y ExecuteReader deben estar contenidos en Try/Catch/Finally
Dr = cmd. ExecuteReader(CommandBehavior.CloseConnection);
Debug.Assert (recuperación ante desastres. HasRows, "Debe tener filas pero no los protegemos"); Se produce un error porque ColumnName es un campo Integer y no mantenga "1,2,3,4,5"

}</CODE>

SOLUCIÓN

Cambie el procedimiento almacenado para adaptarse a la nueva metodología
Una forma fácil de solucionar este problema consiste en crear una tabla temporal utilizando la característica de tabla temporal de Sql Server 2000.  Utilizaremos un bucle simple para analizar hemos pasado en la variable que está separada por un delimitador determinado.  En cada pasada a través del bucle, cada variable se insertará en la tabla temporal.  Por último, utilizaremos una subconsulta para que coincida con los valores de la cláusula LIKE :
<CODE>
CREATE PROCEDURE up_ProcedureHelper
@Tokens como VARCHAR (50), o cualquier otra longitud deberá
AS
DECLARAR @Temp como varchar (50)
Crear tabla #HOLDER(TokenValues VARCHAR(50))
MIENTRAS LEN(@Tokens) > 0
COMENZAR
IF CHARINDEX (',', @Tokens) = 0
COMENZAR
SET @TEMP = @Tokens
SET @Tokens = ''
Insertar en VALUES(@Temp) #Holder (TokenValues)
FIN
ELSE
COMENZAR
SET @Temp = LEFT (@Tokens, CHARINDEX(',', @Tokens)-1)
Insertar en VALUES(@Temp) #Holder (TokenValues)
SET @Tokens = RIGHT (@Tokens, LEN(@Tokens)-LEN(@Temp)-1)
FIN
FIN
Seleccionar * desde [TableName]
DONDE [NombreDelCampo] IN (SELECT TokenValues de #Holder)
</CODE>
 
Por lo tanto, suponiendo que el parámetro "1,2,3,4,5", tendremos una tabla temporal denominada #Holder con 5 filas y los valores 1,2,3,4 y 5 respectivamente.  Esta tabla existirá mientras la conexión que se creó en vivo.  Tan pronto como lo cerramos que la tabla se eliminará automáticamente.  En circunstancias normales es absolutamente imprescindible que se acordará de que se cierre el.  ¿Desea arriesgarse aquí y esperamos que se cierra la conexión, utilice un bloquear en sus procedimientos y llame al método desde allí.   Si está utilizando C# , puede ajustar el bloque en el uso de una instrucción junto con su bloque y asegúrese de que el es así como de (el mismo principio se aplica con todos los demás tipos de conexión).
<CODE>
cadena cs = ConfigurationSettings.AppSettings("ConnectString"));
uso (SqlConnection cn = new {SqlConnection(cs))
SqlCommand cmd = new SqlCommand ("up_ProcedureHelper", ConnectionName);
      cmd.CommandType = CommandType.StoredProcedure;
cmd. Parameters.Add ("@Token", "1,2,3,4,5");
CN. () Abierto; //In realidad Connection.Open y ExecuteReader deben estar contenidos en Try/Catch/Finally
Dr = cmd. ExecuteReader(CommandBehavior.CloseConnection);
Debug.Assert (recuperación ante desastres. HasRows, "Debe tener filas pero no los protegemos"); Ahora es correcta la aserción - y tenemos 5 filas.
}
</CODE>

MÁS INFORMACIÓN

un procedimiento almacenado parametrizado de SQL Server
Una excelente explicación de y cuándo es una buena solución
Una excelente explicación de por qué las consultas
Cómo convertir instrucciones SQL dinámicas mediante programación a las consultas
Propiedades

Id. de artículo: 555167 - Última revisión: 15 feb. 2017 - Revisión: 1

Microsoft ADO.NET 1.1, Microsoft ADO.NET 1.0

Comentarios