Funciones definidas por el usuario con valores de tabla

Funciones definidas por el usuario con valores de tabla

  • Las funciones definidas por el usuario con valores de tabla son las funciones que devuelven un tipo de datos table.
  • Estas funciones son una alternativa eficaz  a las vistas. (Ver vistas).
  • Las funciones definidas por el usuario con valores de tabla pueden ser utilizadas cuando se permitan expresiones de vista o de tabla en las consultas Transact-SQL.
  • Las funciones definidas por el usuario con valores de tabla pueden contener instrucciones adicionales mejorando la lógica de las vistas.
  • Las vistas sólo permiten a una única instrucción SELECT, las funciones definidas por el usuario puede tener joins y agrupamientos.
  • Una función definida por el usuario con valores de tabla puede reemplazar también a procedimientos almacenados que devuelven un solo conjunto de resultados.
  • Al usar una función definida por el usuario con valores de tabla, este se escribe en la cláusula FROM de una instrucción Transact-SQL, a la función se le deben dar los valores para cada parámetro especificado en la creación.



Componentes de una función definida por el usuario con valores de tabla

Los componentes son: 

  1. La cláusula RETURNS: especifica el nombre de una variable de retorno local para la tabla devuelta por la función. En la cláusula RETURNS se define la estructura de la tabla.
  2. Las instrucciones Transact-SQL del cuerpo de la función generan e insertan filas en la variable de retorno definida por la cláusula RETURNS.
  3. La instrucción RETURN que devielve las filas insertadas en la variable desde la función en formato tabular. La instrucción RETURN no tiene argumentos.

Ejemplos

Usando la base de datos Northwind
use Northwind
go

Ejercicio 1
Crear una función definida por el usuario con valores de tabla que muestre los proveedores de un determinado país.  Para visualizar el resultado se incluirá el nombre del país

— Creamos la función

Create function dbo.fduProveedorPorPais (@NombrePais nvarchar(15))
returns @Proveedores Table (Codigo int, Nombre nvarchar(40),
Contacto nvarchar(30), Pais nvarchar(15))
As
Begin
insert @Proveedores
select S.SupplierID, S.CompanyName,
S.ContactName, S.Country
from Suppliers As S
where S.Country = @NombrePais
Return
End
go

Usando la función definida por el usuario con valor de tabla.
Para los proveedores de Estados Unidos (USA)

select * from dbo.fduProveedorPorPais(‘USA’)
go

Ejercicio 2
Crear una función definida por el usuario con valores de tabla que permite mostrar los productos de una determinada categoría. Muestre el valor del Stock para cada producto.
Creamos la función

Create function dbo.fduProductosPorCategoria (@CodigoCategoria int)
returns @Productos Table (Codigo int, Descripcion nvarchar(40),
Precio Numeric(9,2), Stock Numeric(9,2), ValorStock Numeric(9,2))
As
Begin
insert @Productos
select P.ProductID, P.ProductName,
P.UnitPrice, P.UnitsInStock, P.UnitPrice*P.UnitsInStock
from Products As P
where P.CategoryID= @CodigoCategoria
Return
End
go

Utilizar la función para los productos de la categoria 2

Select * from dbo.fduProductosPorCategoria(2)
go

Ejercicio 3
Crear una función definida por el usuario con valores de tabla que permite mostrar los clientes que hicieron compras en un rango de fechas cualquiera. Las fechas serán los parámetros de la función. Se incluirá la cantidad de órdenes y la carga total de todas las órdenes (Freight) (Ver Joins) (Ver Agrupamientos)
Creamos la función

Create function dbo.fduClientesRangoFecha (@FechaInicial Date, @FechaFinal Date)
Returns @ClientesPorRangoFechas Table
(Codigo nchar(5), Nombre varchar(100), CantidadOrdenes Numeric(9,2),
MontoComprado Numeric(9,2))
As
Begin
Insert @ClientesPorRangoFechas
select C.CustomerID, C.CompanyName,
Count(O.OrderID), Sum(O.Freight)
from Customers As C
join Orders As O on C.CustomerID = O.CustomerID
where O.OrderDate Between @FechaInicial and @FechaFinal
group by C.CustomerID, C.CompanyName
Return
End
go

Mostrar los clientes y la cantidad de órdenes y el monto de la carga para el rango de fechas entre 01/07/1997 y 15/07/1997

select * from dbo.fduClientesRangoFecha(’01/07/1997′,’15/07/1997′)
go