Creación de funciones definidas por el usuario (motor de base de datos)

En este tema se describe cómo crear una función definida por el usuario en SQL Server mediante Transact-SQL.

En este tema

Antes de comenzar:

limitaciones y restricciones

Seguridad
Para crear una función definida por el usuario:

Creación de una función escalar

Creación de una función con valores de tabla

Antes de empezar

Limitaciones y restricciones

Las funciones definidas por el usuario no se pueden usar para realizar acciones que modifiquen el estado de la base de datos.
Las funciones definidas por el usuario no pueden contener una cláusula OUTPUT INTO que tenga una tabla como destino.
Las funciones definidas por el usuario no pueden devolver varios conjuntos de resultados. Use un procedimiento almacenado si necesita devolver varios conjuntos de resultados.
El control de errores está restringido en una función definida por el usuario. Una UDF no admite TRY...CATCH, @ERROR, o RAISERROR.
Las funciones definidas por el usuario no pueden llamar a un procedimiento almacenado, pero pueden llamar a un procedimiento almacenado extendido.
Las funciones definidas por el usuario no pueden usar tablas temporales o SQL dinámicas. Se permiten variables de tabla.
No se permiten instrucciones SET en una función definida por el usuario.
No se permite la cláusula FOR XML
Las funciones definidas por el usuario pueden anidarse; es decir, una función definida por el usuario puede invocar a otra. El nivel de anidamiento se incrementa cuando la función llamada inicia la ejecución y disminuye cuando la función llamada finaliza la ejecución. Las funciones definidas por el usuario se pueden anidar hasta 32 niveles. Exceder los límites máximos de anidamiento provoca que toda la cadena de funciones de llamada falle. Cualquier referencia al código administrado desde una función definida por el usuario de Transact-SQL se considera como un nivel respecto al límite de anidamiento de 32 niveles. Los métodos invocados desde el código administrado no cuentan con este límite.
Las siguientes instrucciones de Service Broker no se pueden incluir en la definición de una función definida por el usuario Transact-SQL:
- EMPEZAR CONVERSACIÓN DE DIÁLOGO
- FIN DE LA CONVERSACIÓN
- ACCEDER A GRUPO DE CONVERSACIÓN
- MOVER CONVERSACIÓN
- RECIBIR
- ENVIAR

Seguridad

Permisos

Requiere el permiso CREATE FUNCTION en la base de datos y el permiso ALTER en el esquema en el que se va a crear la función. Si la función especifica un tipo definido por el usuario, requiere el permiso EXECUTE en el tipo .

Funciones escalares

En el ejemplo siguiente se crea una función escalar de varios estados en la base de datos AdventureWorks2012. La función toma un valor de entrada, un ProductIDy devuelve un único valor de datos, la cantidad agregada del producto especificado en el inventario.

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 usa la ufnGetInventoryStock función para devolver la cantidad de inventario actual de los productos que tienen entre ProductModelID 75 y 80.

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

funciones de Table-Valued

En el ejemplo siguiente se crea una función con valores tipo tabla en línea en la base de datos AdventureWorks2012. La función toma un parámetro de entrada, un identificador de cliente (almacén) y devuelve las columnas ProductID, Namey el agregado de ventas de año a fecha como YTD Total para cada producto vendido al almacén.

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  
);

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

SELECT * FROM Sales.ufn_SalesByStore (602);

En el ejemplo siguiente se crea una función con valores de tabla en la base de datos AdventureWorks2012. La función toma un único parámetro de entrada, y EmployeeID devuelve una lista de todos los empleados que informan al empleado especificado directamente o indirectamente. A continuación, se invoca la función que especifica el identificador de empleado 109.

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);

Véase también

Funciones definidas por el usuario
CREATE FUNCTION (Transact-SQL)

Last updated on 2017-06-13

Compartir a través de