como pasar un array en un procedimiento almacenado de SQL Server

Encontramos la respuesta a esta incógnita, al menos eso esperamos. Si tienes inquietudes puedes dejarlo en el apartado de comentarios y sin dudarlo te responderemos

Solución:

SQL Server 2008 (o posterior)

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

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, 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 = x.i.value('(./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 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'…

---

Encuentro que el método de pasar a través de parámetros con valores de tabla simplifica la capacidad de mantenimiento de una solución que lo usa y, a menudo, tiene un mayor rendimiento en comparación con otras implementaciones, incluidas XML y string terrible.

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 en 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 reutiliza para muchos procedimientos almacenados. Al igual que con este ejemplo, el requisito común es pasar a través de una lista de punteros de ID, el nombre de la función describe qué contexto deben representar esos ID, el nombre del tipo debe ser genérico.

Según mi experiencia, al crear una expresión delimitada a partir de los ID de los empleados, existe una solución delicada y agradable para este problema. Sólo debe crear un string expresión como ';123;434;365;' en el cual 123, 434 y 365 son algunos identificadores de empleados. Al llamar al siguiente procedimiento y pasarle esta expresión, puede obtener los registros deseados. Fácilmente puede unirse a la “otra tabla” en esta consulta. Esta solución es adecuada en todas las versiones del servidor SQL. 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

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 de SqlDb.Structured.

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

Eres capaz de proteger nuestro cometido poniendo un comentario o valorándolo te damos las gracias.

---

---

Utiliza Nuestro Buscador