Funciones definidas por el usuario – Ejercicio

FUNCIONES DEFINIDAS POR EL USUARIO

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



Incluyendo algo de teoría las FDU son:

  1. Las que retorna un valor

CREATE FUNCTION Esquema.NombreFuncion(Parámetros)  RETURNS TipoDato

AS

BEGIN

     Cuerpo

RETURN Expresión

END

2. Las que retorna una Tabla

CREATE FUNCTION Esquema.NombreFuncion(Parámetros)

AS  RETURNS TABLE

RETURN InstruccionSelect

 

— 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

— PruebaExec 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