• Artículo

Tipos de funciones

SQL Server 2008 admite las funciones definidas por el usuario y las funciones del sistema integradas.

Funciones escalares

Las funciones escalares definidas por el usuario devuelven un único valor de datos del tipo definido en la cláusula RETURNS. En una función escalar insertada no hay cuerpo de la función; el valor escalar es el resultado de una sola instrucción. Para una función escalar de varias instrucciones, el cuerpo de la función, definido en un bloque BEGIN...END, contiene una serie de instrucciones de Transact-SQL que devuelven el único valor. El tipo devuelto puede ser de cualquier tipo de datos excepto text, ntext, image, cursor y timestamp.

En los ejemplos siguientes se crea una función escalar de varias instrucciones. La función toma un valor de entrada, ProductID, y devuelve un valor de devolución único, la cantidad agregada del producto especificado en el inventario.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL
    DROP FUNCTION ufnGetInventoryStock;
GO
CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)
RETURNS int 
AS 
-- Returns the stock level for the product.
BEGIN
    DECLARE @ret int;
    SELECT @ret = SUM(p.Quantity) 
    FROM Production.ProductInventory p 
    WHERE p.ProductID = @ProductID 
        AND p.LocationID = '6';
     IF (@ret IS NULL) 
        SET @ret = 0;
    RETURN @ret;
END;
GO

En el ejemplo siguiente se utiliza la función ufnGetInventoryStock para devolver la cantidad de inventario actual de aquellos productos que tienen un ProductModelID entre 75 y 80.

USE AdventureWorks2008R2;
GO
SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply
FROM Production.Product
WHERE ProductModelID BETWEEN 75 and 80;
GO

Funciones con valores de tabla

Las funciones con valores de tabla definidas por el usuario devuelven un tipo de datos table. Las funciones con valores de tabla insertados no tienen cuerpo; la tabla es el conjunto de resultados de una sola instrucción SELECT.

En el ejemplo siguiente se crea una función con valores de tabla insertados. La función toma un parámetro de entrada, Id. de cliente (almacén), y devuelve las columnas ProductID, Name, y el agregado de las ventas del año hasta la fecha como YTD Total para cada producto vendido en el almacén.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN 
(
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
    FROM Production.Product AS P 
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

En el ejemplo siguiente se invoca la función y se especifica el identificador de cliente 602.

SELECT * FROM Sales.ufn_SalesByStore (602);

En una función con valores de tabla de varias instrucciones, el cuerpo de la función, definido en un bloque, BEGIN…END, contiene una serie de instrucciones de Transact-SQL que generan e insertan filas en la tabla que serán devueltos.

En el ejemplo siguiente se crea una función con valores de tabla. La función toma un único parámetro de entrada, EmployeeID, y devuelve una lista de todos los empleados que dependen directa o indirectamente del empleado especificado. La función se invoca luego especificando el empleado ID 109.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL
    DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE 
(
    EmployeeID int primary key NOT NULL,
    FirstName nvarchar(255) NOT NULL,
    LastName nvarchar(255) NOT NULL,
    JobTitle nvarchar(50) NOT NULL,
    RecursionLevel int NOT NULL
)
--Returns a result set that lists all the employees who report to the 
--specific employee directly or indirectly.*/
AS
BEGIN
WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns
    AS (
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n
        FROM HumanResources.Employee e 
            INNER JOIN Person.Person p 
            ON p.BusinessEntityID = e.BusinessEntityID
        WHERE e.BusinessEntityID = @InEmpID
        UNION ALL
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor
        FROM HumanResources.Employee e 
            INNER JOIN EMP_cte
            ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
            INNER JOIN Person.Person p 
            ON p.BusinessEntityID = e.BusinessEntityID
        )
-- copy the required columns to the result of the function 
   INSERT @retFindReports
   SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
   FROM EMP_cte 
   RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
FROM dbo.ufn_FindReports(1); 

GO

Funciones integradas

SQL Server proporciona funciones integradas para ayudarle a realizar diversas operaciones. No se pueden modificar. Puede utilizar funciones integradas en instrucciones Transact-SQL para:

Las funciones integradas devuelven tipos de datos escalares o table. Por ejemplo, @@ERROR devuelve 0 si la última instrucción Transact-SQL se ejecutó correctamente. Si la instrucción generó un error, @@ERROR devuelve el número de error. Y la función SUM(parameter) devuelve la suma de todos los valores del parámetro.