Tipos de funciones

Funciones escalares

Las funciones escalares definidas por el usuario devuelven un único valor de datos del tipo definido en la cláusula RETURNS. Las funciones escalares simples no tienen un cuerpo de función; el valor escalar es el resultado de una instrucción de función única (a menudo una instrucción SELECT). En las funciones escalares de varias instrucciones, el cuerpo de la función se define en un bloque BEGIN...END y contiene una serie de instrucciones de Transact-SQL que devuelven un único valor. El tipo devuelto puede ser de cualquier tipo de datos excepto text, ntext, image, cursor, spatial, hierarchyID y timestamp.

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

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

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

USE AdventureWorks;
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

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 AdventureWorks;
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 'YTD 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
    WHERE SH.CustomerID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

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

SELECT * FROM Sales.ufn_SalesByStore (602);

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

En el ejemplo siguiente se crea una función con valores de tabla. La función toma el único parámetro de entrada EmployeeID y devuelve una lista de todos los empleados que dependen del identificador de empleado especificado 109 directa o indirectamente. A continuación, el identificador de empleado 109 se utiliza como parámetro de entrada en el ejemplo y se devuelve una lista de los empleados en la tabla de resultados.

USE AdventureWorks;
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,
    Name nvarchar(255) NOT NULL,
    Title nvarchar(50) NOT NULL,
    EmployeeLevel int NOT NULL,
    Sort nvarchar (255) NOT NULL
)
--Returns a result set that lists all the employees who report to the 
--specific employee directly or indirectly.*/
AS
BEGIN
   WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS
    (SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)
     FROM HumanResources.Employee AS e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
     WHERE e.EmployeeID = @InEmpID
   UNION ALL
     SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + 
                 LastName)
     FROM HumanResources.Employee as e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID
          JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
-- copy the required columns to the result of the function 
   INSERT @retFindReports
   SELECT EmployeeID, Name, Title, EmployeeLevel, Sort
   FROM DirectReports 
   RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM dbo.ufn_FindReports(109)
ORDER BY Sort;
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:

• Tener acceso a información de las tablas del sistema de SQL Server sin tener acceso a las tablas del sistema directamente. Para obtener más información, vea Usar las funciones del sistema.

• Realizar tareas habituales como SUM, GETDATE o IDENTITY. Para obtener más información, vea Funciones (Transact-SQL).

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.

Historial de cambios

Vea también

Referencia

Otros recursos