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 tabla y unirla a otra tabla. Pero la lista de empleados debe pasarse como parámetro desde C#.

Jun 19 '12 20:06 Sergey

SQL Server 2016 (o más reciente)

Puede pasar una lista delimitada o JSONy usar STRING_SPLIT()o OPENJSON().

STRING_SPLIT():

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List varchar(max)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT value FROM STRING_SPLIT(@List, ',');
END
GO
EXEC dbo.DoSomethingWithEmployees @List = '1,2,3';

OPENJSON():

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List varchar(max)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT value FROM OPENJSON(CONCAT('["',
    REPLACE(STRING_ESCAPE(@List, 'JSON'), 
    ',', '","'), '"]')) AS j;
END
GO
EXEC dbo.DoSomethingWithEmployees @List = '1,2,3';

Escribí más sobre esto aquí:

Manejando una cantidad desconocida de parámetros en SQL Server
División de cadenas ordenada en SQL Server con OPENJSON

SQL Server 2008 (o más reciente)

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

CREATE TYPE dbo.IDList
AS TABLE
(
  ID INT
);
GO

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List AS dbo.IDList READONLY
AS
BEGIN
  SET NOCOUNT ON;
  
  SELECT ID FROM @List; 
END
GO

Ahora en tu código C#:

// Obtain your list of ids to send, this is just an example call to a helper utility function
int[] employeeIds = GetEmployeeIds();

DataTable tvp = new DataTable();
tvp.Columns.Add(new DataColumn("ID", typeof(int)));

// populate DataTable from your List here
foreach(var id in employeeIds)
    tvp.Rows.Add(id);

using (conn)
{
    SqlCommand cmd = new SqlCommand("dbo.DoSomethingWithEmployees", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter tvparam = cmd.Parameters.AddWithValue("@List", tvp);
    // these next lines are important to map the C# DataTable object to the correct SQL User Defined Type
    tvparam.SqlDbType = SqlDbType.Structured;
    tvparam.TypeName = "dbo.IDList";
    // execute query, consume results, etc. here
}

Servidor SQL 2005

Si está utilizando SQL Server 2005, seguiría recomendando una función de división en lugar de 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 = 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

Ahora su procedimiento almacenado puede ser simplemente:

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

Y en tu código C# solo tienes que pasar la lista como '1,2,3,12'...

Considero que el método de pasar parámetros con valores de tabla simplifica el mantenimiento de una solución que lo utiliza y, a menudo, tiene un mayor rendimiento en comparación con otras implementaciones, incluidas XML y división de cadenas.

Las entradas están claramente definidas (nadie tiene que adivinar si el delimitador es una coma o un punto y coma) y no tenemos dependencias de otras funciones de procesamiento que no sean obvias sin inspeccionar el código del procedimiento almacenado.

En comparación con las soluciones que involucran un esquema XML definido por el usuario en lugar de UDT, esto implica una cantidad similar de pasos, pero en mi experiencia es un código mucho más simple de administrar, mantener y leer.

En muchas soluciones, es posible que solo necesite uno o algunos de estos UDT (tipos definidos por el usuario) que puede reutilizar para muchos procedimientos almacenados. Al igual que en este ejemplo, el requisito común es pasar por una lista de punteros de ID, el nombre de la función describe qué contexto deben representar esos ID y el nombre del tipo debe ser genérico.

Jun 19 '2012 16:06 Aaron Bertrand

Según mi experiencia, al crear una expresión delimitada a partir de los ID de empleado, existe una solución agradable y complicada para este problema. Solo debe crear una expresión de cadena como ';123;434;365;'in-what 123y algunos ID de empleado 434. 365Al llamar al siguiente procedimiento y pasarle esta expresión, puede recuperar los registros que desee. Puede unirse fácilmente a "otra mesa" en esta consulta. Esta solución es adecuada en todas las versiones de SQL Server. Además, en comparación con el uso de variables de tabla o tablas temporales, es una solución mucho más 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

Dec 02 '2014 15:12 Hamed Nazaktabar

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

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

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();
}

Jun 19 '2012 13:06 Levi W

Debe pasarlo como 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)

Luego puedes usar la tabla temporal para unirte a tus mesas. Definimos arrayOfUlong como un esquema XML integrado para mantener la integridad de los datos, pero no es necesario hacerlo. Recomiendo usarlo, así que aquí hay un código rápido para asegurarse de obtener siempre un XML con palabras largas.

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

Jun 19 '2012 13:06 Fedor Hajdu

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. Existen numerosos ejemplos si desea escribir el suyo propio, o simplemente puede descargar la biblioteca SQL# gratuita de funciones CLR (que escribí, pero la función String_Split y muchas otras son completamente gratuitas).
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 el text()Función XML. 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.
Usar TVP es excelente (suponiendo que esté usando al menos SQL Server 2008 o posterior), ya que los datos se transmiten al proceso y se muestran previamente analizados y fuertemente tipados como una variable de tabla. SIN EMBARGO, en la mayoría de los casos, almacenar todos los datos significa DataTableduplicar los datos en la memoria a medida que se copian de la colección original. Por lo tanto, utilizar el DataTablemétodo de pasar TVP no funciona bien para conjuntos de datos más grandes (es decir, no se escala bien).
XML, a diferencia de las listas delimitadas simples de Ints o Strings, puede manejar más de matrices unidimensionales, al igual que los TVP. Pero al igual que el DataTablemétodo TVP, XML no escala bien ya que duplica con creces el tamaño de los datos en la 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 no son muy grandes todavía pero crecen constantemente, entonces el IEnumerablemétodo TVP es la mejor opción ya que transmite los datos a SQL Server (como el DataTablemétodo), PERO no requieren cualquier 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 al procedimiento almacenado T-SQL

Sep 13 '2014 22:09 Solomon Rutzky