Cómo pasar una matriz a un procedimiento almacenado de SQL Server

¿Cómo pasar una matriz a un procedimiento almacenado de SQL Server?

Por ejemplo, tengo una lista de empleados. Quiero usar esta lista como una tabla y unirla a otra tabla. Pero la lista de empleados debe pasar como parámetro de C #.

Como ya tiene una lista, creo que hay formas más sencillas que XML.

SQL Server 2008 (o más reciente)

Primero, en su base de datos, cree los siguientes dos objetos:

CREATE TYPE dbo.EmployeeList AS TABLE ( EmployeeID INT ); GO CREATE PROCEDURE dbo.DoSomethingWithEmployees @List AS dbo.EmployeeList READONLY AS BEGIN SET NOCOUNT ON; SELECT EmployeeID FROM @List; END GO 

Ahora en tu código C #:

 DataTable tvp = new DataTable(); // define / populate DataTable from your List here using (conn) { SqlCommand cmd = new SqlCommand("dbo.DoSomethingWithEmployees", conn); cmd.CommandType = CommandType.StoredProcedure; SqlParameter tvparam = cmd.Parameters.AddWithValue("@List", tvp); tvparam.SqlDbType = SqlDbType.Structured; // execute query, consume results, etc. here } 

SQL Server 2005

Si está utilizando SQL Server 2005, aún recomendaría una función dividida sobre XML. Primero, crea una función:

 CREATE FUNCTION dbo.SplitInts ( @List VARCHAR(MAX), @Delimiter VARCHAR(255) ) RETURNS TABLE AS RETURN ( SELECT Item = CONVERT(INT, Item) FROM ( SELECT Item = xivalue('(./text())[1]', 'varchar(max)') FROM ( SELECT [XML] = CONVERT(XML, '' + REPLACE(@List, @Delimiter, '') + '').query('.') ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y WHERE Item IS NOT NULL ); GO 

Ahora su procedimiento almacenado puede ser:

 CREATE PROCEDURE dbo.DoSomethingWithEmployees @List VARCHAR(MAX) AS BEGIN SET NOCOUNT ON; SELECT EmployeeID = Item FROM dbo.SplitInts(@List, ','); END GO 

Y en su código C # solo tiene que pasar la lista como '1,2,3,12'

Recomiendo que compare el mantenimiento y el rendimiento de estas opciones con el método que seleccionó.

De acuerdo con mi experiencia, al crear una expresión delimitada de los employeeIDs, hay una solución astuta y agradable para este problema. Solo debe crear una expresión de cadena como ';123;434;365;' in-which 123 , 434 y 365 son algunos employeeIDs. Al llamar al siguiente procedimiento y pasarle esta expresión, puede recuperar los registros que desee. Fácilmente puede unirse a la “otra tabla” en esta consulta. Esta solución es adecuada en todas las versiones de SQL Server. Además, en comparación con el uso de tabla variable o tabla de temperatura, es una solución muy rápida y optimizada.

 CREATE PROCEDURE dbo.DoSomethingOnSomeEmployees @List AS varchar(max) AS BEGIN SELECT EmployeeID FROM EmployeesTable -- inner join AnotherTable on ... where @List like '%;'+cast(employeeID as varchar(20))+';%' END GO 

Use un parámetro con valores de tabla para su procedimiento almacenado.

Cuando lo transfiera desde C #, agregará el parámetro con el tipo de datos de SqlDb.Structured.

Vea aquí: http://msdn.microsoft.com/en-us/library/bb675163.aspx

Ejemplo:

 // Assumes connection is an open SqlConnection object. using (connection) { // Create a DataTable with the modified rows. DataTable addedCategories = CategoriesDataTable.GetChanges(DataRowState.Added); // Configure the SqlCommand and SqlParameter. SqlCommand insertCommand = new SqlCommand( "usp_InsertCategories", connection); insertCommand.CommandType = CommandType.StoredProcedure; SqlParameter tvpParam = insertCommand.Parameters.AddWithValue( "@tvpNewCategories", addedCategories); tvpParam.SqlDbType = SqlDbType.Structured; // Execute the command. insertCommand.ExecuteNonQuery(); } 

Debe pasarlo como un parámetro XML.

Editar: código rápido de mi proyecto para que tenga una idea:

 CREATE PROCEDURE [dbo].[GetArrivalsReport] @DateTimeFrom AS DATETIME, @DateTimeTo AS DATETIME, @HostIds AS XML(xsdArrayOfULong) AS BEGIN DECLARE @hosts TABLE (HostId BIGINT) INSERT INTO @hosts SELECT arrayOfUlong.HostId.value('.','bigint') data FROM @HostIds.nodes('/arrayOfUlong/u') as arrayOfUlong(HostId) 

Luego puede usar la tabla de temperatura para unirse a sus tablas. Definimos arrayOfUlong como un esquema XML integrado para mantener la integridad de los datos, pero no tiene que hacer eso. Recomiendo usarlo así que aquí hay un código rápido para asegurarte de que siempre obtienes un XML con largos.

 IF NOT EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = 'xsdArrayOfULong') BEGIN CREATE XML SCHEMA COLLECTION [dbo].[xsdArrayOfULong] AS N'        '; END GO 

El contexto siempre es importante, como el tamaño y la complejidad de la matriz. Para listas pequeñas y medianas, varias de las respuestas publicadas aquí están bien, aunque se deben hacer algunas aclaraciones:

  • Para dividir una lista delimitada, un divisor basado en SQLCLR es el más rápido. Hay numerosos ejemplos a su alrededor si quiere escribir el suyo, o puede simplemente descargar la libre biblioteca SQL # de funciones CLR (que escribí, pero la función String_Split, y muchas otras, son completamente gratuitas).
  • La división de matrices basadas en XML puede ser rápida, pero necesita usar XML basado en atributos, no XML basado en elementos (que es el único que se muestra en las respuestas aquí, aunque el ejemplo de @ AaronBertrand es el mejor ya que su código está usando el Función XML de text() Para más información (es decir, análisis de rendimiento) sobre el uso de XML para dividir listas, consulte “Uso de XML para pasar listas como parámetros en SQL Server” de Phil Factor.
  • El uso de TVP es excelente (suponiendo que esté utilizando al menos SQL Server 2008 o posterior) a medida que los datos se transmiten al proceso y aparecen pre-analizados y fuertemente tipados como una variable de tabla. SIN EMBARGO, en la mayoría de los casos, el almacenamiento de todos los datos en DataTable significa duplicar los datos en la memoria a medida que se copia desde la colección original. Por lo tanto, usar el método DataTable para pasar los TVP no funciona bien para conjuntos de datos más grandes (es decir, no escala bien).
  • XML, a diferencia de las listas delimitadas simples de Ints o Strings, puede manejar matrices de más de una dimensión, al igual que los TVP. Pero al igual que con el método TVP de DataTable , XML no se escala bien, ya que duplica la cantidad de datos en la memoria, ya que necesita, además, tener en cuenta la sobrecarga del documento XML.

Dicho todo esto, si los datos que está usando son grandes o no son muy grandes pero crecen constantemente, entonces el método IEnumerable TVP es la mejor opción ya que transmite los datos a SQL Server (como el método DataTable ), PERO no lo hace No requiere ninguna duplicación de la colección en la memoria (a diferencia de cualquiera de los otros métodos). Publiqué un ejemplo del código SQL y C # en esta respuesta:

Pasar diccionario a procedimiento almacenado T-SQL

No hay soporte para matriz en el servidor sql, pero hay varias formas en que puede pasar la colección a un proceso almacenado.

  1. Al usar datatable
  2. Mediante el uso de XML. Intente convertir su colección en un formato xml y luego páselo como entrada a un procedimiento almacenado.

El siguiente enlace puede ayudarte

pasar colección a un procedimiento almacenado

He estado buscando en todos los ejemplos y las respuestas de cómo pasar cualquier matriz al servidor SQL sin la molestia de crear un nuevo tipo de tabla, hasta que encontré este enlace , a continuación se muestra cómo lo apliqué a mi proyecto:

–El siguiente código va a obtener una matriz como parámetro e inserta los valores de esa –array en otra tabla

 Create Procedure Proc1 @UserId int, //just an Id param @s nvarchar(max) //this is the array your going to pass from C# code to your Sproc AS declare @xml xml set @xml = N'' + replace(@s,',','') + '' Insert into UserRole (UserID,RoleID) select @UserId [UserId], t.value('.','varchar(max)') as [RoleId] from @xml.nodes('//root/r') as a(t) END 

Espero que lo disfrutes

Esto te ayudara. 🙂 Sigue los próximos pasos,

  1. Abra el Diseñador de consultas
  2. Copiar Pega el siguiente código tal como está, creará la función que convertirá la cadena a Int.

     CREATE FUNCTION dbo.SplitInts ( @List VARCHAR(MAX), @Delimiter VARCHAR(255) ) RETURNS TABLE AS RETURN ( SELECT Item = CONVERT(INT, Item) FROM ( SELECT Item = xivalue('(./text())[1]', 'varchar(max)') FROM ( SELECT [XML] = CONVERT(XML, '' + REPLACE(@List, @Delimiter, '') + '').query('.') ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y WHERE Item IS NOT NULL ); GO 
  3. Cree el siguiente procedimiento almacenado

      CREATE PROCEDURE dbo.sp_DeleteMultipleId @List VARCHAR(MAX) AS BEGIN SET NOCOUNT ON; DELETE FROM TableName WHERE Id IN( SELECT Id = Item FROM dbo.SplitInts(@List, ',')); END GO 
  4. Ejecute este SP usando exec sp_DeleteId '1,2,3,12' esta es una cadena de Id que quiere eliminar,

  5. Convierte tu matriz a cadena en C # y la pasa como un parámetro de Procedimiento almacenado

     int[] intarray = { 1, 2, 3, 4, 5 }; string[] result = intarray.Select(x=>x.ToString()).ToArray(); 

     SqlCommand command = new SqlCommand(); command.Connection = connection; command.CommandText = "sp_DeleteMultipleId"; command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@Id",SqlDbType.VARCHAR).Value=result ; 

Esto eliminará varias filas, todo lo mejor

Me tomó mucho tiempo resolver esto, por si alguien lo necesita …

Esto está basado en el método SQL 2005 en la respuesta de Aaron, y usando su función SplitInts (Acabo de eliminar el param delim, ya que siempre usaré comas). Estoy usando SQL 2008 pero quería algo que funcione con conjuntos de datos tipeados (XSD, TableAdapters) y sé que los parámetros de cadena funcionan con eso.

Intentaba que su función funcionara en una cláusula de tipo “where in (1,2,3)”, y sin tener la suerte de la manera directa. Así que primero creé una tabla temporal, y luego hice una combinación interna en lugar de “dónde estaba”. Aquí está mi uso de ejemplo, en mi caso quería obtener una lista de recetas que no contienen ciertos ingredientes:

 CREATE PROCEDURE dbo.SOExample1 ( @excludeIngredientsString varchar(MAX) = '' ) AS /* Convert string to table of ints */ DECLARE @excludeIngredients TABLE (ID int) insert into @excludeIngredients select ID = Item from dbo.SplitInts(@excludeIngredientsString) /* Select recipies that don't contain any ingredients in our excluded table */ SELECT r.Name, r.Slug FROM Recipes AS r LEFT OUTER JOIN RecipeIngredients as ri inner join @excludeIngredients as ei on ri.IngredientID = ei.ID ON r.ID = ri.RecipeID WHERE (ri.RecipeID IS NULL)