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

Basado en mi experiencia, al crear una expresión delimitada de los employeeIDs, hay una solución complicada y agradable para este problema. Solo debe crear una expresión de cadena como ';123;434;365;' en la que 123, 434 y 365 son algunos employeeIDs. Al llamar al siguiente procedimiento y pasarle esta expresión, puede obtener los registros deseados. Fácilmente puede unir 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 tabla variable o tabla temporal, 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

Utilice un parámetro con valor de tabla para su procedimiento almacenado.

Cuando lo pase desde C# agregará el parámetro con el tipo de datos de SqlDb.Estructurar.

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

Entonces puede usar la tabla temporal para unirse con sus tablas. Definimos arrayOfUlong como un esquema XML integrado para mantener la integridad de los datos, pero no tiene que hacerlo. Recomiendo usarlo, así que aquí hay un código rápido para asegurarse de que siempre obtenga un XML con longs.

IF NOT EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = 'xsdArrayOfULong')
BEGIN
    CREATE XML SCHEMA COLLECTION [dbo].[xsdArrayOfULong]
    AS N'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="arrayOfUlong">
        <xs:complexType>
            <xs:sequence>
                <xs:element maxOccurs="unbounded"
                            name="u"
                            type="xs:unsignedLong" />
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>';
END
GO

El contexto siempre es importante, como el tamaño y la complejidad del array. Para listas de tamaño pequeño a mediano, 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 alrededor si usted quiere escribir su propio, o usted puede descargar el libre SQL # biblioteca de funciones CLR (que escribí, pero el String_Split función, y muchos otros, son completamente gratis).
• Dividir matrices basadas en XML puede ser rápido, pero necesita usar XML basado en atributos, no XML basado en elementos (que es el único tipo que se muestra en las respuestas aquí, aunque el ejemplo XML de @AaronBertrand es el mejor, ya que su código usa la función XML text(). Para obtener 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.
• Utilizando TVPs es genial (suponiendo que esté utilizando al menos SQL Server 2008, o más reciente) ya que los datos se transmiten al proc y aparecen pre-analizados y fuertemente escritos como una variable de tabla. SIN embargo, en la mayoría de los casos, almacenar todos los datos en DataTable significa duplicar los datos en la memoria a medida que se copian de la colección original. Por lo tanto, usar el método DataTable de pasar TVPs 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 Cadenas, pueden manejar más que arreglos unidimensionales, al igual que TVPs. Pero también al igual que el método TVP DataTable, XML no escala bien, ya que más del doble del tamaño de datos en memoria, ya que necesita tener en cuenta adicionalmente la sobrecarga del documento XML.

Dicho todo esto, SI los datos que está utilizando son grandes o aún 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 requiere ninguna duplicación de la colección en memoria (a diferencia de cualquiera de los otros métodos). He publicado un ejemplo del código SQL y C # en esta respuesta:

Pase el diccionario al Procedimiento almacenado T-SQL

No hay soporte para array en sql server, pero hay varias maneras de pasar la colección a un proc almacenado .

1. Usando datatable
2. Usando XML.Intente convertir su colección en un formato xml y luego pasarla como entrada a un procedimiento almacenado

El siguiente enlace puede ayudarte

Pasar la colección a un procedimiento almacenado

He estado buscando a través de todos los ejemplos y respuestas de cómo pasar cualquier matriz a sql server 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 un Array como Parámetro e insertar los valores de ese -- 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'<root><r>' + replace(@s,',','</r><r>') + '</r></root>'

    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 ayudará. :) Siga los siguientes pasos,

1. Abra el Diseñador de consultas

2. Copie y Pegue 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 = x.i.value('(./text())[1]', 'varchar(max)')
           FROM ( SELECT [XML] = CONVERT(XML, '<i>'
           + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').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 desea eliminar,

5. Usted convierte su matriz en cadena en C# y la pasa como un Procedimiento Almacenado parámetro

   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 llevó mucho tiempo resolver esto, así que en caso de que alguien lo necesite...

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

Estaba tratando de hacer que su función funcionara en una cláusula de tipo" where in (1,2,3)", y no tuve suerte con camino directo. Así que creé una tabla temporal primero, y luego hice una unión interna en lugar del "dónde". Aquí está mi ejemplo de uso, 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)