FDU con variables tipo tabla SQL Server

Funciones definidas por el usuario con variables tipo tabla en SQL Server

Las funciones definidas por el usuario, FDU, son porciones de código guardadas en la base de datos y que retornan un valor o retornan un conjunto de datos, dependiendo de como han sido diseñadas. (Ver Funciones definidas por el usuario)

Las variables tipo tabla son tipos de variables que son definidas como una tabla, su uso es temporal y son muy útiles en procesos donde se manejan registros y se necesitan almacenar posiblemente de manera temporal. (Ver variables) (Ver variables tipo tabla).

Este artículo muestra como usar las variables tipo tabla en FDU que retornan una tabla, se recomienda siempre comparar los resultados de las consultas usando Joins (Ver Joins), usando Subconsultas (Ver Subconsultas) y el uso de FDU. (Ver comparación entre Joins, Subconsultas y FDU)

Ejercicios

Usando Northwind
use Northwind
go

Ejercicio 1

Crear una FDU que retorne una tabla con los productos de una categoría. Incluir el valor del Stock que es el resultado de multiplicar Precio por cantidad en Stock. La FDU recibirá el código de la categoría.

Create function dbo.fduProductosPorCategoria (@CodigoCategria int)
Returns @ProductosCategoria Table
(Código int, Descripción nvarchar(40), Unidad nvarchar(20),
Precio Numeric(9,2), Stock Numeric(9,2), [Valor Stock] Numeric(9,2))
As
Begin
insert into @ProductosCategoria
select P.ProductID, P.ProductName, P.QuantityPerUnit,
P.UnitPrice, P.UnitsInStock, P.UnitPrice*P.UnitsInStock
from Products As P
where P.CategoryID = @CodigoCategria
Return
End
go

Note que se ha usado una variable llamada @ProductosCategoria de tipo tabla.
Para mostrar los productos de la categoría 1

select * from dbo.fduProductosPorCategoria(1)
go

Ejercicio 2

Mostrar las órdenes de un cliente en un rango de fechas, incluir el total
de la orden, para el total crear otra FDU que retorna un valor.

La FDU que retorna el total de la orden

Create function dbo.fduRetornaTotalOrden(@NumeroOrden int)
Returns Numeric(9,2)
As
Begin
Declare @TotalOrden Numeric(9,2)
Select @TotalOrden = Sum(D.Quantity * D.UnitPrice)
from [Order Details] As D where D.OrderID = @NumeroOrden
Return @TotalOrden
End
go

Se puede probar la FDU creada calculando el total de la orden 10248, antes se va a mostrar el detalle de la orden.
select * from [Order Details] As D
where D.OrderID = 10248
go

El total de la orden se obtiene multiplicando precios por cantidades, para esta orden el total es la suma de 14 * 12 + 9.8 * 10 + 34.8*5 = 440
La FDU devuelve el valor calculado
select dbo.fduRetornaTotalOrden(10248) As Total
go

La FDU que retorna las órdenes por Cliente.

Create function dbo.fduOrdenesPorClienteRangoFechas
(@CodigoCliente nchar(5), @FechaInicial Date, @FechaFinal Date)
Returns @OrdenesPorClienteFechas Table
([Nº Orden] int, Fecha Date,
Total Numeric(9,2), Ciudad nvarchar(15))
As
Begin
insert into @OrdenesPorClienteFechas
select O.OrderID, O.OrderDate,
dbo.fduRetornaTotalOrden(O.OrderID), O.ShipCity
from Orders As O
where O.CustomerID = @CodigoCliente and
O.OrderDate between @FechaInicial and @FechaFinal
Return
End
go

Para mostrar las órdenes del cliente con código VINET entre Julio y Agosto 1996
select * from dbo.fduOrdenesPorClienteRangoFechas(‘VINET’,’01/07/1996′,’31/08/1996′)
go

Note el total de la orden 10248 de 440.