Mejoras en T-SQL de SQL Server 2008 - El type Table para ser usado como parámetro de un Stored Procedure

Siempre existió la necesidad de pasar información en forma batch, esto es, pasar múltiples registros de información a SQL como parámetro de un Stored Procedure, lo cual tendría un rendimiento superior al pasar de una sola vez una gran cantidad de información y no pasar la información con múltiples invocaciones.
En SQL Server 2000 el parche que se usaba para lograr este efecto era pasar un parámetro con datos XML en una variable varchar(8000) para luego leerlo desde el Stored Procedure con OpenXML.
En SQL Server 2005 se incorporó el tipo de datos xml que facilitó este manejo, ya que por un lado permitía enviar mayor cantidad de información a los 8000 bytes y por el otro habilitaba el manejo de xquery además del OpenXML para leer el parámetro de tipo XML nativo.
Pero en SQL Server 2008 esto todavía se logra de una manera todavía mas sencilla utilizando parámetros de tipo Table.

Un ejemplo vale mas que mil palabras y para empezar a demostrar la nueva característica vamos a crear una base de datos de ejemplo:
CREATE DATABASE [Ejemplo]

Ahora vamos a crear un tabla llamada Clientes que utilizaremos para este ejemplo.
use Ejemplo
go
CREATE TABLE [Clientes]
(
[ID] [int] NOT NULL PRIMARY KEY IDENTITY,
[Nombre] [varchar](100)NOT NULL,
[Apellido] [varchar](100)NOT NULL,
[Email] [varchar](200) NOT NULL
)
GO
--ingresamos algunos registros en la tabla Clientes
INSERT INTO [Clientes] (nombre, apellido, Email)
VALUES('aaa','XYZ', 'aaa@ejemplo.com')
INSERT INTO [Clientes] (nombre, apellido, Email)
VALUES('bbb','XYZ', 'bbb@ejemplo.com')
INSERT INTO [Clientes] (nombre, apellido, Email)
VALUES('ccc','XYZ', 'ccc@ejemplo.com')
GO

podemos probar los datos ingresados con:
SELECT * FROM Clientes

y debieran aparecer los tres registros previamente cargados.

Ahora creamos el tipo de datos (type) de tipo Table:
CREATE TYPE [ClientesUDT] AS TABLE
(
nombre varchar(100) NOT NULL,
apellido varchar(100) NOT NULL,
Email varchar(200) NOT NULL
)
GO
Con cualquiera de las dos sentencias siguientes pueden verificar la creación del tipo:
SELECT name, system_type_id, user_type_id, is_assembly_type, is_table_type
FROM SYS.TYPES WHERE is_table_type = 1
SELECT name, system_type_id, user_type_id, is_assembly_type, is_table_type
FROM SYS.TABLE_TYPES

Obviamente también pueden usar el Management Studio para verificar en forma visual la existencia del nuevo type.

Hecho todo lo anterior, ahora vamos directamente al punto clave de toda esta innovación y vamos a crear un Stored Procedure utilizando un parámetro de tipo Table:
use Ejemplo
GO
CREATE PROCEDURE dbo.AgregaClientes
@ClientesTVP ClientesUDT READONLY --observese que tiene que ser readonly
AS
BEGIN
INSERT INTO dbo.Clientes
SELECT * FROM @ClientesTVP
END
GO

Ahora vamos a probar si todo funciona ok:
/*
** defino una variable de tipo el udf de tipo table previamente creado
*/
DECLARE @ColeccionClientes ClientesUDT
--ingreso algunos datos
INSERT INTO @ColeccionClientes (nombre, apellido, Email)
VALUES('ddd','XYZ', 'ddd@ejemplos.com')
INSERT INTO @ColeccionClientes (nombre, apellido, Email)
VALUES('eee','XYZ', 'eee@ejemplos.com')
INSERT INTO @ColeccionClientes (nombre, apellido, Email)
VALUES('fff','XYZ', 'fff@ejemplos.com')

EXEC AgregaClientes @ColeccionClientes
select * from clientes
GO

Y de esta manera hemos pasado en un solo parámetro varios registros de una sola vez, con todo lo que eso implica a nivel rendimiento y sencillez.

Invocando el Stored Procedure desde .NET 3.5
En la siguiente página se indica detalladamente como invocar el Stored Procedure de este artículo desde un aplicativo .NET 3.5
http://logicanet.blogspot.com/2009/08/usando-el-nuevo-tipo.html

Hugo Román Bernachea
Mail de contacto: SQLServer777@gmail.com

Microsoft Certified DBA
Microsoft Certified Trainer
Twitter: @bernachea


Read More...