C # SQL Server: pasar una lista a un procedimiento almacenado

Solución:

Si está utilizando SQL Server 2008, hay una nueva función llamada Tipo de tabla definido por el usuario. A continuación, se muestra un ejemplo de cómo utilizarlo:

Cree su tipo de tabla definido por el usuario:

CREATE TYPE [dbo].[StringList] AS TABLE(
    [Item] [NVARCHAR](MAX) NULL
);

A continuación, debe usarlo correctamente en su procedimiento almacenado:

CREATE PROCEDURE [dbo].[sp_UseStringList]
    @list StringList READONLY
AS
BEGIN
    -- Just return the items we passed in
    SELECT l.Item FROM @list l;
END

Finalmente, aquí hay algo de sql para usarlo en c #:

using (var con = new SqlConnection(connstring))
{
    con.Open();

    using (SqlCommand cmd = new SqlCommand("exec sp_UseStringList @list", con))
    {
        using (var table = new DataTable()) {
          table.Columns.Add("Item", typeof(string));

          for (int i = 0; i < 10; i++)
            table.Rows.Add("Item " + i.ToString());

          var pList = new SqlParameter("@list", SqlDbType.Structured);
          pList.TypeName = "dbo.StringList";
          pList.Value = table;

          cmd.Parameters.Add(pList);

          using (var dr = cmd.ExecuteReader())
          {
            while (dr.Read())
                Console.WriteLine(dr["Item"].ToString());
          }
         }
    }
}

Para ejecutar esto desde SSMS

DECLARE @list AS StringList

INSERT INTO @list VALUES ('Apple')
INSERT INTO @list VALUES ('Banana')
INSERT INTO @list VALUES ('Orange')

-- Alternatively, you can populate @list with an INSERT-SELECT
INSERT INTO @list
   SELECT Name FROM Fruits

EXEC sp_UseStringList @list

El patrón típico en esta situación es pasar los elementos en una lista delimitada por comas, y luego en SQL dividirlos en una tabla que puede usar. La mayoría de la gente suele crear una función específica para hacer esto como:

 INSERT INTO <SomeTempTable>
 SELECT item FROM dbo.SplitCommaString(@myParameter)

Y luego puedes usarlo en otras consultas.

No, las matrices / listas no se pueden pasar directamente a SQL Server.

Las siguientes opciones están disponibles:

1. Pasar una lista delimitada por comas y luego tener una función en SQL dividir la lista. Lo más probable es que la lista delimitada por comas se pase como un Nvarchar ()
2. Pase xml y tenga una función en SQL Server para analizar el XML para cada valor en la lista
3. Utilice el nuevo tipo de tabla definida por el usuario (SQL 2008)
4. Construya dinámicamente el SQL y pase la lista sin formato como “1,2,3,4” y cree la instrucción SQL. Esto es propenso a ataques de inyección SQL, pero funcionará.

---

---

Utiliza Nuestro Buscador