Tipo de dato definido por el usuario con formato tabla
En SQL Server se pueden crear tipos de datos definidos por el usuario, estos tipos de datos se pueden utilizar cuando hay varios campos con las mismas características, por ejemplo, datos de tipo nvarchar de 100 caracteres de ancho que son obligatorios, se podrá entonces crear un tipo de dato definido por el usuario con esas características y luego al crear las tablas se puede usar este tipo de dato definido por el usuario de manera más sencilla.
Para mas información ver:
Tipos de datos definidos por el usuario
Variables en SQL Server
Inserción de datos
Variables tipo tabla
Procedimientos almacenados
Los tipos de datos definidos por el usuario con formato de tabla son tipos que pueden definirse con la estructura de una tabla, puede usarse también variables tipo tabla o tablas temporales, en este artículo vamos a hacer ejemplos de como guardar un documento que tiene encabezados y detalle como una orden, pedido o factura.
Usando Northwind
use Northwind
go
Ejercicio 1
Crear un Tipo de dato definido por el usuario con formato tabla para Clientes de Mexico.
Primero creamos el tipo de datos definido por el usuario
Create type dbo.ClientesPorPais As Table
(
Codigo nchar(5),
Nombre nvarchar(100),
Contacto nvarchar(50),
Ciudad nvarchar(20),
Direccion nvarchar(50)
)
go
Ver los tipos de datos
select * from sys.types where is_user_defined = 1
go
La imagen muestra la lista de los tipos de datos definidos por el usuario con formato de tabla.
Usando el tipo de dato
Se define una variable con el tipo de dato definido por el usuario con formato tabla, luego se insertan los clientes de México.
Declare @ClientesPais ClientesPorPais
insert into @ClientesPais
select
CustomerID, CompanyName, ContactName, City, Address
from dbo.Customers
where Country = ‘Mexico’
select * from @ClientesPais
go
La imagen muestra el resultado.
Ejercicio 2
En este ejercicio se va a insertar una orden (tabla Orders), obviamente se va a suponer que esa orden tiene determinados productos (tabla Order Details).
Primero se va a insertar una orden sin el uso del parámetro con el tipo de datos definido por el usuario con formato tipo tabla. Para insertar una orden se debe definir el encabezado de la orden en la tabla Orders y el detalle en la tabla «Order Details»
La imagen muestra los campos a insertar en cada tabla.
Inserción de una orden sin el parámetro de tipo tabla
Insertando el encabezado en la tabla Orders, el campo OrderID es Identity, por lo que no es necesario especificar el valor.
insert into dbo.Orders values
(‘ALFKI’,3,’24/02/2023′,’10/02/2023′,’01/03/2023′,
2,10.5,’LuviSoft Factory’,’Av. San Luis 994′, ‘Trujillo’,
‘Tru’,’053-2402′,’Perú’)
go
Para saber el código de la orden usamos la función SCOPE_IDENTITY()
select SCOPE_IDENTITY()
go
El valor es: 11078, es el Id de la orden insertada
Para el detalle
Suponiendo que se vendieron los productos 4,7 y 10
select * from dbo.Products where ProductID in (4,7,10)
go
La imagen muestra el detalle en Productos
Para insertar el detalle suponiendo que se venden 10 unidades del producto 4, 5 unidades del producto 7 y 20 unidades del producto 10.
insert into dbo.[Order Details]
([OrderID], [ProductID], [UnitPrice], [Quantity], [Discount])
values
(11078, 4, 25.30, 10, 0),
(11078, 7, 36.00, 5, 0),
(11078, 10, 37.20, 20, 0)
go
Obviamente se debe ejecutar un proceso que reste las unidades en stock
de los productos vendidos
update dbo.Products set UnitsInStock = UnitsInStock – 10 where ProductID = 4
update dbo.Products set UnitsInStock = UnitsInStock – 5 where ProductID = 7
update dbo.Products set UnitsInStock = UnitsInStock – 20 where ProductID = 10
go
Note que para insertar el detalle son tantas líneas como productos se hayan vendido
en esa orden.
Para ver la orden y el detalle
select
O.OrderID,
P.ProductID,
P.ProductName,
D.Quantity,
D.UnitPrice
from dbo.Orders As O
join dbo.[Order Details] As D on O.OrderID = D.OrderID
join dbo.Products As P on D.ProductID = P.ProductID
where O.OrderID = 11078
go
La orden y su detalle se muestran en la siguiente imagen
Inserción de una orden usando Procedimientos almacenados
Primero, sin usar el tipo definido por el usuario con formato tabla.
Procedimientos almacenados para insertar la orden
Para el Procedimiento almacenado se deberán definir los siguiente parámetros
@OrderID int
@CustomerID nchar(5)
@EmployeeID int
@OrderDate datetime
@RequiredDate datetime
@ShippedDate datetime
@ShipVia int
@Freight money
@ShipName nvarchar(40)
@ShipAddress nvarchar(60)
@ShipCity nvarchar(15)
@ShipRegion nvarchar(15)
@ShipPostalCode nvarchar(10)
@ShipCountry nvarchar(15)
Creando los Procedimientos almacenados, uno para guardar la Orden y otro procedimiento para guardar el detalle correspondiente.
El Procedimiento almacenado para guardar la cabecera reporta el OrderID como resultado, se define por esto un parámetro tipo OutPut para capturar el dato.
Create or alter procedure dbo.spOrdenesGuardarCabecera
(
@CustomerID nchar(5),
@EmployeeID int ,
@OrderDate datetime,
@RequiredDate datetime,
@ShippedDate datetime,
@ShipVia int,
@Freight money,
@ShipName nvarchar(40),
@ShipAddress nvarchar(60),
@ShipCity nvarchar(15),
@ShipRegion nvarchar(15),
@ShipPostalCode nvarchar(10),
@ShipCountry nvarchar(15),
@IDOrden int Output
)
As
insert into dbo.Orders values
(
@CustomerID, @EmployeeID, @OrderDate, @RequiredDate,
@ShippedDate, @ShipVia, @Freight, @ShipName, @ShipAddress,
@ShipCity, @ShipRegion, @ShipPostalCode, @ShipCountry
)
Select @IDOrden = Scope_Identity()
go
Para guardar el detalle en la tabla «Order Details» creamos el procedimiento almacenado como sigue
Create or alter procedure dbo.spDetalleOrdenGuardar
(
@OrderID int,
@ProductID int,
@UnitPrice money,
@Quantity smallint,
@Discount real
)
As
insert into dbo.[Order Details]
([OrderID], [ProductID], [UnitPrice], [Quantity], [Discount])
values
(@OrderID, @ProductID, @UnitPrice, @Quantity, @Discount)
go
Probando los procedimientos almacenados, se venderan los mismos productos 4,7 y 10, pero esta vez 1, 2 y 3 unidades respectivamente.
Begin transaction GuardarFactura
Declare @IdOrden int
Execute dbo.spOrdenesGuardarCabecera
‘ANTON’,2,’24/02/2023′,’10/02/2023′,’01/03/2023′,
2,28.2,’SQL Developers’,’Av. San Blas 994′, ‘Cusco’,
‘Cus’,’053-2321′,’Perú’, @IdOrden OutPut
— El detalle de los productos de la orden, ejecutando el procedimiento para cada uno.
Execute dbo.spDetalleOrdenGuardar @IdOrden, 4, 25.30, 1, 0
Execute dbo.spDetalleOrdenGuardar @IdOrden, 7, 36.00, 2, 0
Execute dbo.spDetalleOrdenGuardar @IdOrden, 10, 37.20, 3, 0
if @@ERROR = 0
Begin
Print ‘Orden guardada exitosamente’
Commit transaction GuardarFactura
End
else
Begin
Print ‘No se pudo insertar la orden’
Rollback transaction GuardarFactura
End
go
Listando las órdenes se generó la 11079
select * from dbo.Orders
go
Importante: Queda para el lector la instrucción para descontar lo vendido, se puede
realizar esta tarea con un Trigger. (Ver Triggers)
Ver la orden 11079
select
O.OrderID,
P.ProductID,
P.ProductName,
D.Quantity,
D.UnitPrice
from dbo.Orders As O
join dbo.[Order Details] As D on O.OrderID = D.OrderID
join dbo.Products As P on D.ProductID = P.ProductID
where O.OrderID = 11079
go
La imagen muestra el resultado
Inserción de la orden y el detalle usando el tipo de dato definido por el usuario con formato de tabla.
Para el detalle se va a crear un tipo de dato con formato de tabla para la inserción de los artículos enviados en la orden. El tipo de dato definido por el usuario con formato de tabla debe tener la misma estructura de la tabla «Order Details»
Los campos son:
OrderID int
ProductID int
UnitPrice money
Quantity smallint
Discount real
Creando el tipo de dato definido por el usuario con formato tabla para el detalle
Create type DetalleOrden As Table
(
ProductID int,
UnitPrice money,
Quantity smallint,
Discount real
)
go
Procedimiento que usa el parámetro con el tipo de datos definido por el usuario con formato tipo tabla creado llamado DetalleOrden, es necesario anotar que el parámetro con el tipo definido por el usuario con formato tabla debe ser de sólo lectura.
Create or alter procedure dbo.spOrdenesGuardarCabeceraDetalle
(
@CustomerID nchar(5),
@EmployeeID int ,
@OrderDate datetime,
@RequiredDate datetime,
@ShippedDate datetime,
@ShipVia int,
@Freight money,
@ShipName nvarchar(40),
@ShipAddress nvarchar(60),
@ShipCity nvarchar(15),
@ShipRegion nvarchar(15),
@ShipPostalCode nvarchar(10),
@ShipCountry nvarchar(15),
@DetalleOrden DetalleOrden readonly,
@IDOrden int Output
)
As
Begin Transaction GuardarFacturaDetalle
insert into dbo.Orders values
(
@CustomerID, @EmployeeID, @OrderDate, @RequiredDate,
@ShippedDate, @ShipVia, @Freight, @ShipName, @ShipAddress,
@ShipCity, @ShipRegion, @ShipPostalCode, @ShipCountry
)
— Capturar el Id de la Orden
Select @IDOrden = Scope_Identity()
— Guardar el detalle
insert into dbo.[Order Details]
([OrderID], [ProductID], [UnitPrice], [Quantity], [Discount])
select @IDOrden, D.ProductID, D.UnitPrice, D.Quantity, D.Discount
from @DetalleOrden As D
if @@ERROR = 0
Begin
Commit transaction GuardarFacturaDetalle
End
else
Begin
Rollback transaction GuardarFacturaDetalle
End
go
Note el tipo de dato definido por el usuario con formato tabla llamado @DetalleOrden utilizado.
Para ejecutar el procedimiento para insertar toda la orden en una sola ejecución se va a definir una variable del tipo definido por el usuario con formato de tabla llamado DetalleOrden, se va a insertar en esta variable los datos de los productos a incluir en la Orden.
Suponiendo que se vendieron los productos 16, 23, 1 y 21, de cada producto 2 unidades
select * from dbo.Products where ProductID in (1,16,21,23)
go
Los productos se muestran en la siguiente imagen
Ejecutar el SP para insertar tanto cabecera como detalle, el tipo de dato definido por el usuario con formato tabla tiene los campos: ProductID , UnitPrice , Quantity y Discount
El cliente que compra será: Ana Trujillo Emparedados, ID ANATR
El empleado sera el 4
Ejecutamos el SP, primero se definen variables, la primera para capturar el número de la orden cuyo dato en la tabla Orders en Identity, luego una variable con el tipo de dato definido por el usuario con formato de tabla para el detalle.
Declare @NumeroOrden int
Declare @DetalleDeOrden DetalleOrden
— Insertando el detalle en el tipo de dato formato tabla.
insert into @DetalleDeOrden
values (1, 24, 2, 0),
(16, 20.94, 2, 0),
(21, 12, 2, 0),
(23, 13.8, 2, 0)
— Ejecutanto el SP
Execute dbo.spOrdenesGuardarCabeceraDetalle
‘ANATR’,4,’15/02/2023′,’01/02/2023′,’24/02/2023′,
2,15.20,’Luvi App Devs’,’Av. Santa Clara 333′, ‘Lima’,
‘Lim’,’051-2402′,’Perú’, @DetalleDeOrden, @NumeroOrden OutPut
go
La orden generada es la 11080
select * from dbo.Orders
go
Ver la orden 11080
select
O.OrderID,
P.ProductID,
P.ProductName,
D.Quantity,
D.UnitPrice
from dbo.Orders As O
join dbo.[Order Details] As D on O.OrderID = D.OrderID
join dbo.Products As P on D.ProductID = P.ProductID
where O.OrderID = 11080
go
El resultado se muestra en la siguiente imagen
Puede notar que para la inserción de todos los artículos en el detalle se usa el tipo definido por el usuario con formato tabla, evitando así tener que ejecutar el procedimiento para insertar cada artículo en el detalle tantan veces como artículos hayan en la orden.