post icon

Enviar ParamArray a un Stored Procedure en SQL Server

Una de las características que siempre consideré necesaria en versiones anteriores a SQL Server 2008 y otros motores comerciales era la capacidad de recibir una colección de datos como parámetro INPUT, así como manejan desde hace bastante tiempo Oracle y PostgreSQL.

El título que he puesto es algo engañoso, pero lo redacté así para llegar con mayor facilidad desde los buscadores. La verdad no existe un tipo de dato Array o Collection dentro de SQL Server hasta hoy día, pero si es posible enviar N valores de una sola vez, incluso mejora conceptualmente al típico paramArray porque al ser un tipo de tabla, puede recibir filas y columnas como valores.

Para ello debemos contar con la versión 2008 o superior de SQL Server y explotar la característica “Tipos de tablas definidos por el usuario“.

Para nuestro ejemplo crearemos una pequeña tabla que contendrá 3 columnas (una int y 2 varchar)

CREATE TYPE [dbo].[testParam] AS TABLE(
	[id] [int] NOT NULL,
	[val1] [varchar](50) NULL,
	[val2] [varchar](50) NULL,
	PRIMARY KEY CLUSTERED
(
	[id] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO

Luego creamos una tabla con fines de demostración, aquí depositaremos todos los datos que se le envíe al parámetro input

CREATE TABLE [dbo].[testing](
	[id] [int] NOT NULL,
	[val1] [varchar](50) NOT NULL,
	[var2] [varchar](50) NOT NULL,
 CONSTRAINT [PK_testing] PRIMARY KEY CLUSTERED
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

El procedimiento almacenado iría así

create procedure test @param testParam readonly
as
insert into dbo.testing
select * from @param;

Con esa tabla ya se puede hacer lo que deseen, en el ejemplo hacemos un INSERT INTO SELECT básico.

Para invocarlo desde el mismo SQL simplemente podemos hacer esto

declare @par testParam

insert into @par  VALUES (1, 'Manuela','Vera')
insert into @par  VALUES (2, 'Josefa','Medina')
insert into @par  VALUES (3, 'Pablo','Garmendia')

execute dbo.test @param = @par

select * from dbo.testing

La pregunta que muchos se harán ¿y cómo programo desde la aplicación cliente? Muy sencilla es la implementación, más de lo que parece. Veamos dos ejemplo, con C# y VB.Net respectivamente.

try
{
	var dataTable = new DataTable();
	dataTable.Columns.Add("Id", typeof(int));
	dataTable.Columns.Add("val1", typeof(string));
	dataTable.Columns.Add("val2", typeof(string));

	dataTable.Rows.Add(1, "Jose", "Rodriguez");
	dataTable.Rows.Add(2, "María", "Benitez");
	dataTable.Rows.Add(3, "Marcela", "Troche");

	using (var conn = new SqlConnection("Server=10.10.10.1;Initial Catalog=devtroce;user ID=devtroce;password=*********"))
	{
		using (var cmd = new SqlCommand("test", conn))
		{
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Parameters.AddWithValue("@param", dataTable);
			conn.Open();
			cmd.ExecuteNonQuery();

			MessageBox.Show("guardado con éxito");
		}
	}
}
catch(Exception ex)
{
	MessageBox.Show(ex.Message);
}
Try
	Dim dataTable As New DataTable()
	dataTable.Columns.Add("Id", Type.GetType("System.Int32"))
	dataTable.Columns.Add("val1", Type.GetType("System.String"))
	dataTable.Columns.Add("val2", Type.GetType("System.String"))

	dataTable.Rows.Add(1, "Jose", "Rodriguez")
	dataTable.Rows.Add(2, "María", "Benitez")
	dataTable.Rows.Add(3, "Marcela", "Troche")

	Using conn As New SqlConnection("Server=10.10.10.1;Initial Catalog=devtroce;user ID=devtroce;password=*********")
		Using cmd As New SqlCommand("test", conn)
			cmd.CommandType = CommandType.StoredProcedure
			cmd.Parameters.AddWithValue("@param", dataTable)
			conn.Open()
			cmd.ExecuteNonQuery()
			MessageBox.Show("Operacion Exitosa")
		End Using
	End Using
Catch ex As Exception
	MessageBox.Show(ex.Message)
End Try

Finalmente pueden hacer

select * from dbo.testing
go

y verán que las tres filas han sido almacenados con 1 sola instrucción INSERT.

Esto podemos utilizarlos en varios escenarios, el más típico sería cuando manejamos datos de cabecera-detalle (master-detail), típicos de los sistemas de facturación y otros.

Algunos puntos a tener en cuenta al utilizar éste objeto son los que siguen, recomiendo que lo lean para evitarse sorpresas en el futuro.

Los tipos de tabla definidos por el usuario tienen las restricciones siguientes:

  • Un tipo de tabla definido por el usuario no se puede utilizar como columna en una tabla o como campo en un tipo estructurado definido por el usuario.
  • Tipos de alias basados en un tipo de tabla definido por el usuario
  • No se permite la opción [NOT FOR REPLICATION].
  • Las restricciones CHECK exigen que las columnas calculadas sean PERSISTED.
  • La clave principal en las columnas calculadas deben ser PERSISTED y NOT NULL.
  • No es posible crear índices no agrupados en un tipo de tabla definido por el usuario a menos que el índice sea el resultado de crear una restricción PRIMARY KEY o UNIQUE en el tipo de tabla definido por el usuario. (SQL Server exige cualquier restricción UNIQUE o PRIMARY KEY por medio del uso de un índice).
  • No es posible modificar la definición de un tipo de la tabla definida por el usuario una vez creado.
  • No es posible llamar a las funciones definidas por el usuario dentro de la definición de columnas calculadas de un tipo de tabla definido por el usuario.

Comentarios desde Facebook:

  1. avatar
    javier Internet Explorer Windows
    20 octubre 2012 at 14:47 #

    otra pregunta estimado GeekZero.. que pasaria si implemento eso para un sistema de punto de venta para supermecados en la cual cada terminal factura al mismo tiempo .. que pasaria si esta facturando un terminal y graba y manda valores al el testparam y en ese momento tambien mandan de otra terminal o pc valores a type testparam? que pasara se combinaran los valores mandados por cada terminal al momento de mandarlo a la otra tabla testing ?.. me gustaria saber la explicacion estimado amigo

  2. avatar
    javier Internet Explorer Windows
    20 octubre 2012 at 13:08 #

    Hola estimado por favor tu crees que podrias poner el codigo para vb6.0 te lo agradeceria bastante un gran saludo 🙂

    • avatar
      Javier PERU Internet Explorer Windows
      6 diciembre 2012 at 16:21 #

      por favor que alguien me apoye pero pasando el arreglo por vb6

  3. avatar
    Edison Cabrera ECUADOR Google Chrome Windows
    9 octubre 2012 at 17:55 #

    Hola: es super practico lo q indicas arriba..y es lo q estoy buscando… pero ¿puedo hacer esto desde .net usando sqldatasource y no SqlCommand? Agradezco mucho tu ayuda. Saludos

    • avatar
      GeekZero PARAGUAY Google Chrome Windows
      9 octubre 2012 at 18:00 #

      Con el wizzard, dificil que puedas hacerlo.

      Podrias convertir tu SqlDataSource en un DataTable, pero lo tendras que hacer por codigo, algo así

      1
      2
      3
      4
      
      DataView dv = new DataView();
      DataTable dt = new DataTable();
      dv = SQLDataSource1.Select(DataSourceSelectArguments.Empty);
      dt = dv.ToTable();
      • avatar
        Edison Cabrera ECUADOR Google Chrome Windows
        9 octubre 2012 at 18:29 #

        Gracias por la ayuda. También veo q puedo pasar la tabla a una variable de sesión y pasarla como parámetro. Tal vez le sirva a alguien eso.

      • avatar
        Javier PERU Internet Explorer Windows
        6 diciembre 2012 at 16:19 #

        GeekZero podrias hacer el mismo ejemplo de pasar esos parametros pero de vb6.0 a sql 2008 es que no se como hacerlo , te lo agradeceria.

        • avatar
          GeekZero PARAGUAY Google Chrome Windows
          6 diciembre 2012 at 16:24 #

          Javier VB6 ya es un lenjuaje sin soporte, no tendrá un tipo de dato equivalente.

          Veo 2 posibles soluciones, migrate a .Net o haz un bucle en VB para envíar de a uno las filas..

          Saludos!

  4. avatar
    Monica Rivera UNITED STATES Mozilla Firefox Windows
    4 octubre 2012 at 18:25 #

    Hola, te podrias comunicar conmigo a la direccion que te envio?

    • avatar
      GeekZero Safari Mac OS
      4 octubre 2012 at 19:28 #

      Hola Mónica, respondido 😀

Trackbacks/Pingbacks

  1. Como enviar ParamArray a un Stored Procedure en Oracle | DevTroce.com UNITED STATES WordPress - 19 octubre 2012

    […] ParamArray a un Stored Procedure en Oracle Hace poco vimos como enviar una lista de valores a un Stored Procedure en SQL Server, en ésta ocasión veremos como hacerlo en Oracle. Este motor tiene soporte para esto hace mucho […]

Responder