Funciones definidas por el usuario FDU

FUNCIONES DEFINIDAS POR EL USUARIO

Las Funciones definidas por el usuario son rutinas que aceptan parámetros de manera opcional,  realizan acciones y devuelven el resultado como un valor o como una tabla.
El valor devuelto puede ser un valor escalar único o un conjunto de resultados.

Las Funciones Definidas por el usuario son de DOS TIPOS:
1. Las que retornan UN VALOR – InLine,se utilizan en otras instrucciones
2. Las que retornan UNA TABLA



Para crear las que devuelven un valor.
Create function Esquema.NombreFuncion([Parámetros]) Returns TipoDato
As
Begin
Instrucciones….
Return ValorRetornado
End
go

Para crear las FDU que retornan UNA TABLA
Create function Esquema.NombreFuncion([Parámetros]) Returns Table
As
Return (Select….)
go

Ejercicios

Usando Northwind
use Northwind
go

FDU que retorne los productos con tengan mas Unidades en Orden que Stock actual
Create function fduProductoCompraUrgente () returns Table
As
Return (select * from Products where UnitsOnOrder > UnitsInStock)
go
Para usar la función creada
select * from fduProductoCompraUrgente()
go

Listar de Pedidos de un cliente, escribir el nombre del cliente
Primero: buscar el código del cliente.
Ejemplo “Universidad SQL”
select CustomerID from Customers  where CompanyName = ‘Universidad SQL’
go
Se comprueba que no existe el cliente
Para el cliente “Antonio Moreno Taquería”
select CustomerID from Customers  where CompanyName = ‘Antonio Moreno Taquería’
go
Resultado: ANTON

Para las Órdenes del Cliente se puede usar una sub consulta. (Ver Sub consultas)
SELECT * FROM Orders  where   CustomerID = (select CustomerID from Customers
where CompanyName = ‘Antonio Moreno Taquería’)
go

En una FDU
Create function fduOrdenesPorCliente(@Cliente nvarchar(40)) Returns Table
As
Return (SELECT * FROM Orders
where CustomerID = (select CustomerID from Customers
where CompanyName =@Cliente ))
go

Usando la FDU anterior, las Órdenes para ‘Antonio Moreno Taquería’
select * from fduOrdenesPorCliente(‘Antonio Moreno Taquería’)
go

Productos con precio mayor a valor ingresado
Create function fduProductosPrecioHaciaArriba(@Precio Numeric(9,2)) Returns Table
As
Return (select * from Products where UnitPrice > @Precio)
go
Usar la FDU
select * from fduProductosPrecioHaciaArriba(80)
go

Pedidos de un cliente en un rango de fechas
Create function fduOrdenesPorClienteRangoFechas(@Cliente nvarchar(40), @FechaInicial Date,
@FechaFinal Date) Returns Table
As
Return (SELECT * FROM Orders
where CustomerID = (select CustomerID from Customers where CompanyName =@Cliente )
and (OrderDate >= @FechaInicial and OrderDate <= @FechaFinal)
)
go
Usar la FDU fduOrdenesPorClienteRangoFechas
select * from fduOrdenesPorClienteRangoFechas(‘Antonio Moreno Taquería’,’01/06/1997′,’31/12/1997′)
go

Listado de las categorías y el valor del Stock de cada una
Valor del Stock para la categoria 1
select SUM(UnitPrice * UnitsInStock) from Products where CategoryID = 1
go
La FDU para calcular el valor del stock
Create function fduValorStockPorCategoria(@CodigoCategoria int)
Returns Numeric(9,2)
As
Begin
— Variable para capturar el Valor
Declare @ValorTotalStock Numeric(9,2)
set @ValorTotalStock = (select SUM(UnitPrice * UnitsInStock)
from Products where CategoryID = @CodigoCategoria )
Return @ValorTotalStock
End
go
Usar la FDU fduValorStockPorCategoria
select C.CategoryID As ‘Código’, C.CategoryName As ‘Categoría’,
dbo.fduValorStockPorCategoria(C.CategoryID) As ‘Valor Stock’
from Categories As C  order by [Valor Stock] desc
go


Costo consultando el Plan de Ejecución: 0.0146903

El mismo resultado usando Subconsultas
select C.CategoryID As ‘Código’, C.CategoryName As ‘Categoría’,
(select SUM(UnitPrice * UnitsInStock)
from Products As P where P.CategoryID = C.CategoryID)
As ‘Valor Stock’
from Categories As C
order by [Valor Stock] desc
go
Costo consultando el Plan de Ejecución: 0.0196404



Se puede ver que la FDU es más rápida que la sub consulta.

Eliminar una FDU
Drop function fduOrdenesPorCliente
go