Funciones definidas por el usuario FDU
Las funciones definidas por el usuario permiten obtener resultados que las funciones propias de SQL Server no pueden mostrarnos, son de mucha utilidad para optimizar el trabajo de consultas con parámetros. Para mejor información ver Funciones definidas por el usuario.
Se pueden usar en ocasiones para obtener el mismo resultado Joins y Subconsultas.
Las Funciones Definidas por el usuario son de DOS TIPOS:
- Las que retornan UN VALOR – InLine,se utilizan en otras instrucciones
- 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
— Usando Northwind
use Northwind
go
Crear una función para retornar el total de Categorías
Create function dbo.fCategoriasCuenta() Returns Int
As
Begin
Declare @CantidadCategorias int
Select @CantidadCategorias=count(*) from categories
Return @CantidadCategorias
End
go
— Prueba:
Select ‘Existen: ‘ + Ltrim(Str(dbo.fCategoriasCuenta()))+ ‘ categorías’
go
— Reportar el total de artículos en stock de categoria 1
select sum(UnitsInStock) from products where categoryid=1
go
Reportar el total de artículos en stock de categoria 5
select sum(UnitsInStock) from products where categoryid=5
go
Crear una función que reporte el total de artículos en Stock de cualquier categoría
Create function dbo.fnStockxCategoria(@CodigoCategoria int) Returns Int
As
Begin
Declare @TotalStock int
Select @TotalStock = sum(UnitsInStock) from products where CategoryId = @CodigoCategoria
Return @TotalStock
End
go
Categoria 1
Select ‘Existe: ‘+Ltrim(Str(dbo.fnStockxCategoria(1))) + ‘ artículos’
go
Categoria 5
Select ‘Existe: ‘+Ltrim(Str(dbo.fnStockxCategoria(5))) + ‘ artículos’
go
Implementar la función anterior pero asignando el nombre de la categoría
— Cuantos artículos en stock hay de Confections
Declare @CodigoCategoria int
Select @CodigoCategoria=categoryid from categories where Categoryname=’Confections’
Select ‘Existe: ‘+Ltrim(Str(dbo.fnStockxCategoria(@CodigoCategoria))) + ‘ artículos’
go
Cuantos artículos hay de categoría Huesos, podemos incluir la FDU en un Procedimiento (Ver procedimientos)
Create procedure spVerCantidadArticulosStockxCategoria ( @NombreCategoria nvarchar(15) )
As
if exists (select * from categories where categoryname = @NombreCategoria)
Begin
Declare @IDCategoria int
select @IDCategoria = CategoryId from Categories where CategoryName=@NombreCategoria
select ‘Existen : ‘ + Ltrim(Str(dbo.fnStockxCategoria(@IDCategoria))) + ‘ Productos’
End
Else
Begin
Print ‘No existe la categoria ‘ + @NombreCategoria
End
go
Prueba
Exec spVerCantidadArticulosStockxCategoria ‘Beverages’
Función que permita mostrar los clientes y la cantidad comprada de una determinada ciudad.
Create function dbo.fCantidadxClientexPais(@Pais nvarchar(15))
Returns Table
As
Return select C.CustomerID As ‘Código Cliente’,
C.CompanyName As ‘Empresa’, C.Country,
Sum(O.Freight) As Monto
from Customers As C
join Orders As O on C.CustomerID = O.CustomerID
where C.Country= @Pais
group by C.CustomerID, C.CompanyName, C.Country
go
— Ver los clientes de Perú
select * from dbo.fCantidadxClientexPais(‘Perú’)
go
Ver los clientes de Francia
select * from dbo.fCantidadxClientexPais(‘France’)
go
— Comprobando que el país exista
Create procedure spCantidadxClientesxPais
(
@Pais nvarchar(15)
)
As
if exists(select Country from Customers where Country =@Pais)
Begin
select * from dbo.fCantidadxClientexPais(@Pais)
End
Else
Begin
Select ‘No existe el país especificado’
End
go
— Pruebas
Exec spCantidadxClientesxPais ‘France’
go