Crear funciones definidas por el usuario (motor de base de datos)Create User-defined Functions (Database Engine)

SE APLICA A: síSQL Server síAzure SQL Database noAzure Synapse Analytics (SQL DW) noAlmacenamiento de datos paralelosAPPLIES TO: YesSQL Server YesAzure SQL Database NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

En este tema se describe cómo crear una función definida por el usuario en SQL ServerSQL Server con Transact-SQLTransact-SQL.This topic describes how to create a user-defined function (UDF) in SQL ServerSQL Server by using Transact-SQLTransact-SQL.

Antes de comenzarBefore You Begin

Limitaciones y restriccionesLimitations and restrictions

  • Las funciones definidas por el usuario no se pueden utilizar para realizar acciones que modifican el estado de la base de datos.User-defined functions cannot be used to perform actions that modify the database state.

  • Las funciones definidas por el usuario no pueden tener una cláusula OUTPUT INTO que tenga una tabla como destino.User-defined functions cannot contain an OUTPUT INTO clause that has a table as its target.

  • Las funciones definidas por el usuario no pueden devolver varios conjuntos de resultados.User-defined functions can not return multiple result sets. Utilice un procedimiento almacenado si necesita devolver varios conjuntos de resultados.Use a stored procedure if you need to return multiple result sets.

  • El control de errores está restringido en una función definida por el usuario.Error handling is restricted in a user-defined function. Una UDF no admite TRY...CATCH, @ERROR o RAISERROR.A UDF does not support TRY...CATCH, @ERROR or RAISERROR.

  • Las funciones definidas por el usuario no pueden llamar a un procedimiento almacenado, pero pueden llamar a un procedimiento almacenado extendido.User-defined functions cannot call a stored procedure, but can call an extended stored procedure.

  • Las funciones definidas por el usuario no pueden utilizar tablas temporales o SQL dinámicas.User-defined functions cannot make use of dynamic SQL or temp tables. Se permiten las variables de tabla.Table variables are allowed.

  • Las instrucciones SET no se permiten en una función definida por el usuario.SET statements are not allowed in a user-defined function.

  • No se admite la cláusula FOR XML.The FOR XML clause is not allowed.

  • Las funciones definidas por el usuario se pueden anidar; es decir, una función definida por el usuario puede llamar a otra.User-defined functions can be nested; that is, one user-defined function can call another. El nivel de anidamiento aumenta cuando se empieza a ejecutar la función llamada y disminuye cuando se termina de ejecutar la función llamada.The nesting level is incremented when the called function starts execution, and decremented when the called function finishes execution. Las funciones definidas por el usuario se pueden anidar hasta un máximo de 32 niveles.User-defined functions can be nested up to 32 levels. Si se superan los niveles máximos de anidamiento, la cadena completa de funciones de llamada produce un error.Exceeding the maximum levels of nesting causes the whole calling function chain to fail. Cualquier referencia a código administrado desde una función Transact-SQL definida por el usuario cuenta como uno de los 32 niveles de anidamiento.Any reference to managed code from a Transact-SQL user-defined function counts as one level against the 32-level nesting limit. Los métodos invocados desde el código administrado no cuentan para este límite.Methods invoked from within managed code do not count against this limit.

  • Las instrucciones de Service Broker siguientes no se pueden incluir en la definición de una función Transact-SQLTransact-SQL definida por el usuario:The following Service Broker statements cannot be included in the definition of a Transact-SQLTransact-SQL user-defined function:

    • BEGIN DIALOG CONVERSATION

    • END CONVERSATION

    • GET CONVERSATION GROUP

    • MOVE CONVERSATION

    • RECEIVE

    • SEND

PermisosPermissions

Se 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.Requires CREATE FUNCTION permission in the database and ALTER permission on the schema in which the function is being created. Si la función especifica un tipo definido por el usuario, requiere el permiso EXECUTE en el tipo.If the function specifies a user-defined type, requires EXECUTE permission on the type.

Funciones escalaresScalar Functions

En el ejemplo siguiente se crea una función escalar (UDF escalar) de varias instrucciones en la base de datos AdventureWorks2012AdventureWorks2012.The following example creates a multi-statement scalar function (scalar UDF) in the AdventureWorks2012AdventureWorks2012 database. 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.The function takes one input value, a ProductID, and returns a single data value, the aggregated quantity of the specified product in inventory.

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; 

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.The following example uses the ufnGetInventoryStock function to return the current inventory quantity for products that have a ProductModelID between 75 and 80.

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

Nota

Para obtener más información y ejemplos de funciones escalares, vea CREATE FUNCTION (Transact-SQL).For more information and examples of scalar functions, see CREATE FUNCTION (Transact-SQL).

Funciones con valores de tablaTable-Valued Functions

En el ejemplo siguiente se crea una función insertada con valores de tabla(TVF) en la base de datos AdventureWorks2012AdventureWorks2012.The following example creates an inline table-valued function (TVF) in the AdventureWorks2012AdventureWorks2012 database. 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.The function takes one input parameter, a customer (store) ID, and returns the columns ProductID, Name, and the aggregate of year-to-date sales as YTD Total for each product sold to the store.

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.The following example invokes the function and specifies customer ID 602.

SELECT * FROM Sales.ufn_SalesByStore (602);  

En el ejemplo siguiente se crea una función con valores de tabla de varias instrucciones (MSTVF) en la base de datos AdventureWorks2012AdventureWorks2012.The following example creates a multi-statement table-valued function (MSTVF) in the AdventureWorks2012AdventureWorks2012 database. 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.The function takes a single input parameter, an EmployeeID and returns a list of all the employees who report to the specified employee directly or indirectly. La función se invoca luego especificando el empleado ID 109.The function is then invoked specifying employee ID 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  

En el ejemplo siguiente se invoca la función y se especifica el identificador de cliente 1.The following example invokes the function and specifies employee ID 1.

SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel  
FROM dbo.ufn_FindReports(1);  

Nota

Para obtener más información y ejemplos de funciones insertadas con valores de tabla (TVF insertadas) y funciones con valores de tabla de varias instrucciones (MSTVF), vea CREATE FUNCTION (Transact-SQL).For more information and examples of inline table-valued functions (inline TVFs) and multi-statement table-valued functions (MSTVFs), see CREATE FUNCTION (Transact-SQL).

Prácticas recomendadasBest Practices

Si una función definida por el usuario (UDF) no se crea con la cláusula SCHEMABINDING, los cambios que se realicen en los objetos subyacentes pueden afectar a la definición de la función y generar resultados inesperados al invocarla.If a user-defined function (UDF) is not created with the SCHEMABINDING clause, changes that are made to underlying objects can affect the definition of the function and produce unexpected results when it is invoked. Recomendamos implementar uno de los siguientes métodos para garantizar que la función no queda sin actualizar como consecuencia de los cambios realizados en sus objetos subyacentes:We recommend that you implement one of the following methods to ensure that the function does not become outdated because of changes to its underlying objects:

  • Especifique la cláusula WITH SCHEMABINDING cuando vaya a crear la UDF.Specify the WITH SCHEMABINDING clause when you are creating the UDF. Así se asegura de que no se pueden modificar los objetos a los que se hace referencia en la definición de la función a menos que también se modifique la función.This ensures that the objects referenced in the function definition cannot be modified unless the function is also modified.

  • Ejecute el procedimiento almacenado sp_refreshsqlmodule después de modificar cualquier objeto que se especifique en la definición de la UDF.Execute the sp_refreshsqlmodule stored procedure after modifying any object that is specified in the definition of the UDF.

Si se va a crear una UDF que no tiene acceso a datos, especifique la opción SCHEMABINDING.If creating a UDF that does not access data, specify the SCHEMABINDING option. Esto impedirá que el optimizador de consultas genere operadores de cola de impresión innecesarios para planes de consultas que implican estas UDF.This will prevent the query optimizer from generating unnecessary spool operators for query plans involving these UDFs. Para obtener más información sobre las colas de impresión, vea Referencia de operadores lógicos y físicos del plan de presentación.For more information on spools, see Showplan Logical and Physical Operators Reference. Para obtener más información sobre la creación de una función enlazada a un esquema, vea Funciones enlazadas a esquema.For more information on creating a schema bound function, see Schema-bound functions.

Es posible unirse a una MSTVF en una cláusula FROM, pero puede provocar un rendimiento incorrecto.Joining to an MSTVF in a FROM clause is possible, but can result in poor performance. SQL ServerSQL Server no puede usar todas las técnicas optimizadas en algunas instrucciones que se pueden incluir en una MSTVF, lo que conllevaría que el plan de consulta no alcanzase un nivel óptimo.is unable to use all the optimized techniques on some statements that can be included in a MSTVF, resulting in a suboptimal query plan. Para lograr el mejor rendimiento, siempre que sea posible, use combinaciones entre las tablas base en lugar de funciones.To obtain the best possible performance, whenever possible use joins between base tables instead of functions.

Importante

Las MSTVF tienen una estimación de cardinalidad fija de 100 a partir de SQL Server 2014 (12.x)SQL Server 2014 (12.x), y de 1 en versiones anteriores de SQL ServerSQL Server.MSTVFs have a fixed cardinality guess of 100 starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x), and 1 for earlier SQL ServerSQL Server versions.
A partir de SQL Server 2017 (14.x)SQL Server 2017 (14.x), la optimización de un plan de ejecución en el que se usan MSTVF puede aprovechar la ejecución intercalada, lo que resulta en el uso de la cardinalidad real en lugar de la heurística anterior.Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x), optimizing an execution plan that uses MSTVFs can leverage interleaved execution, which results in using actual cardinality instead of the above heuristics.
Para obtener más información, vea Ejecución intercalada de funciones con valores de tabla de múltiples instrucciones.For more information, see Interleaved execution for multi-statement table valued functions.

Nota

ANSI_WARNINGS no se respeta al pasar parámetros en un procedimiento almacenado o una función definida por el usuario, ni cuando se declaran y se establecen variables en una instrucción por lotes.ANSI_WARNINGS is not honored when you pass parameters in a stored procedure, user-defined function, or when you declare and set variables in a batch statement. Por ejemplo, si una variable se define como char(3) y después se establece en un valor de más de tres caracteres, los datos se truncan hasta el tamaño definido y la instrucción INSERT o UPDATE se ejecuta correctamente.For example, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds.

Consulte tambiénSee Also

Funciones definidas por el usuario User-Defined Functions
CREATE FUNCTION (Transact-SQL) CREATE FUNCTION (Transact-SQL)
ALTER FUNCTION (Transact-SQL) ALTER FUNCTION (Transact-SQL)
DROP FUNCTION (Transact-SQL) DROP FUNCTION (Transact-SQL)
DROP PARTITION FUNCTION (Transact-SQL) DROP PARTITION FUNCTION (Transact-SQL)
Obtenga más ejemplos en la comunidadMore examples in the community