Home > Articulos & Tutoriales, Desarrollo, SQL Server > SQL Server–Parametros Tipo tabla

SQL Server–Parametros Tipo tabla


Buenas!!!!

Generalmente durante el proceso de desarrollo se busca realizar la menor cantidad de transacciones sobre la base de datos, esto sobre un escenario desconectado, el ejemplo mas claro es al momento de querer registrar multiples datos sobre la base de datos. generalmente este proceso se realiza de manera secuencial, es decir registrando dato por dato pero este proceso aunque efectivo no es del todo optimo y seguro.

Para ayudar a resolver esta situacion, en la version 2008 de SQL Server, viene un nuevo tipo de dato “Tabla”, este tipo de dato nos permirita manejar multiples registros dentro de una sola variable. De esta forma podremos enviar toda una tabla desde nuestro aplicativo y recibirla como parametro en un procedimiento almacenado a travez de una variable de tipo tabla para su procesamiento.

Para enternderlo mas claramente, a continuacion a travez de un ejemplo mostrare como de manera sencilla se pueden realizar una transaccion de varios registros y almacenarlos en una base de datos sql server utilizando parametros tipo tabla.

Primero se creara una base de datos de ejemplo, 2 tablas y se registraran datos de ejemplo.

-- Creacion de Base de Datos de Ejemplo
CREATE DATABASE TVPDemo
GO

USE TVPDemo
GO

-- Creacion de Tabla de Ordenes
CREATE TABLE dbo.TVPOrder (
  CustCode varchar(5),
  OrdNo int identity,
  OrdDate datetime
)
GO

-- Creacion de Tabla de Items
CREATE TABLE dbo.TVPItem (
  OrdNo int,
  ProdCode int,
  Qty int
)
GO

Como comentaba anteriormente, si se desean ingresar multiples registros a la tabla el procedimiento correcto y mas acostumbrado a usar era el siguiente. Primer se crea un procedimiento que recibe como parametros cada uno de las variables a almacenar.

-- Creacion de Procedimiento de Registro de Ordenes
CREATE PROCEDURE dbo.TVPOrderInsert
(
  @CustCode varchar(5),
  @OrdNo integer output,
  @OrdDate datetime output)
AS
  SET @OrdDate = GETDATE();
  INSERT INTO dbo.TVPOrder (OrdDate, CustCode)
    VALUES (@OrdDate, @CustCode);
  SELECT @OrdNo = SCOPE_IDENTITY()
GO

-- Creacion de Procedimiento de Registro de Items
CREATE PROCEDURE dbo.TVPItemInsert
(
  @OrdNo integer,
  @ProdCode integer,
  @Qty integer)
AS
  INSERT INTO dbo.TVPItem (OrdNo, ProdCode, Qty)
    VALUES (@OrdNo, @ProdCode, @Qty)
GO

Para el registro multiple usando estos procedimientos se llama uno por uno segun la cantidad de datos.

-- Por ultimo se registran algunos datos de ejemplo utilizando los procedimientos creados
DECLARE @ordno int;
DECLARE @orddate datetime;

EXEC dbo.TVPOrderInsert 'ALFKI', @ordno output, @orddate output

EXEC dbo.TVPItemInsert @ordno, 111, 1
EXEC dbo.TVPItemInsert @ordno, 222, 2
EXEC dbo.TVPItemInsert @ordno, 333, 3
EXEC dbo.TVPItemInsert @ordno, 444, 4
EXEC dbo.TVPItemInsert @ordno, 555, 5
GO

 

El procedimiento anterior aunque es correcto como vemos tiene que realizar varias peticiones al servidor, lo cual implica demora en la realizacion de una sola opracion (registro).

image

Para agilizar este proceso vamos a utilizar un tipo de dato tabla y enviarlo como parametro al procedimiento almacenado, mayor info en http://msdn.microsoft.com/es-es/library/bb675163.aspx

A continuacion crearemos el tipo de dato tabla con 2 columnas de tipo entero.

----------------Creacion del tipo--------------
IF (SELECT COUNT(*) FROM sys.table_types
WHERE name = 'TVPParam' AND schema_id = 1) = 0
  CREATE TYPE dbo.TVPParam AS TABLE(ProdCode integer, Qty integer)
GO

Ahora se creara un procedimiento que reciba como parametro una variable tipo tabla y registre los datos que este contenga en la tabla de ordenes y de items.

-- Procedimiento de registro con parametroi tipo tabla
CREATE PROCEDURE dbo.TVPOrderEntry
(
  @CustCode varchar(5),
  @Items TVPParam READONLY,
  @OrdNo integer output,
  @OrdDate datetime output)
AS
  SET @OrdDate = GETDATE();

  INSERT INTO TVPOrder (OrdDate, CustCode)
    VALUES (@OrdDate, @CustCode); 

  SELECT @OrdNo = SCOPE_IDENTITY(); 

  INSERT INTO TVPItem (OrdNo, ProdCode, Qty)
    SELECT @OrdNo, ProdCode, Qty FROM @Items
GO

El el query anterior se puede ver como en la segunda sentencia INSERT se registran los datos que provienen de la variable @Items que se declaro y recibio como tipo tabla TVPParam.

Ahora se probara el procedimiento creando una varable tabla y enviadola como parametro.

-- Dentro de una variable tabla se ingresaran datos
-- y se enviara como parametro al procedimiento
DECLARE @ordno int;
DECLARE @orddate datetime;
DECLARE @t TVPParam ;
INSERT INTO @t VALUES(666,6), (777,7), (888,8);
EXEC dbo.TVPOrderEntry 'BEAUC', @t, @ordno output, @orddate output;
GO

Como podemos ver en la imagen se han registrado los datos almacendos en la variable tipo tabla.

image

Para concluir, los paramtros tipo tabla ayudan a simplificar las transacciones sobre la base de datos al momento de enviar multiples valores sobre este, mas aun cuando no se conoce la cantidad de datos a enviar.

Gerardo Ramos – Microsoft Student Partner

  1. Jose Luis Hernandez
    September 27, 2011 at 6:19 pm

    Excelente POST me latio lo del store con la variable tipo tabla excelente para ingresar registros tipo maestro detalle

  1. July 1, 2010 at 1:10 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: