Nota:
El acceso a esta página requiere autorización. Puede intentar iniciar sesión o cambiar directorios.
El acceso a esta página requiere autorización. Puede intentar cambiar los directorios.
Aplica a:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSistema de Plataforma de Analítica (PDW)Base de datos SQL en Microsoft Fabric
En este artículo se describe cómo crear una función definida por el usuario (UDF) en SQL Server mediante Transact-SQL.
Limitaciones
Las funciones definidas por el usuario no se pueden utilizar para realizar acciones que modifican el estado de la base de datos.
Las funciones definidas por el usuario no pueden tener una cláusula OUTPUT INTO que tenga una tabla como destino.
Las funciones definidas por el usuario no pueden devolver varios conjuntos de resultados. Utilice 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, @ERRORo 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 utilizar tablas temporales o SQL dinámicas. Se permiten las variables de tabla.
SET No se permiten instrucciones en una función definida por el usuario (por ejemplo, SET NOCOUNT ON;). La asignación de valores de variable puede usar SET.
No se permite la cláusula FOR XML.
Funciones anidadas definidas por el usuario
Las funciones definidas por el usuario se pueden anidar. Es decir, una función definida por el usuario puede llamar a otra. 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.
Las funciones definidas por el usuario se pueden anidar hasta un máximo de 32 niveles. Si se superan los niveles máximos de anidamiento, la cadena completa de funciones de llamada produce un error. 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.
Los métodos invocados desde el código administrado no cuentan para este límite.
Declaraciones de Service Broker
Las siguientes instrucciones de Service Broker no se pueden incluir en la definición de una función Transact-SQL definida por el usuario:
BEGIN DIALOG CONVERSATIONEND CONVERSATIONGET CONVERSATION GROUPMOVE CONVERSATIONRECEIVESEND
Funciones de efecto secundario
Las siguientes funciones integradas no deterministas no se pueden usar en una función definida por el usuario (UDF) Transact-SQL.
NEWIDNEWSEQUENTIALIDRANDTEXTPTR
Si hace referencia a una de estas funciones dentro de una UDF, obtendrá el siguiente error:
Msg 443, Level 16, State 1
Invalid use of a side-effecting operator <operator> within a function.
Para consultar una lista de las funciones de sistema integradas deterministas y no deterministas, vea Funciones deterministas y no deterministas.
Para solucionar este problema, puede encapsular la función con efectos secundarios en una vista y llamar a la vista desde una función.
Permisos
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. Si la función especifica un tipo definido por el usuario, requiere el permiso EXECUTE en el tipo.
Ejemplos de funciones escalares
Función escalar (UDF escalar)
El siguiente ejemplo crea una función escalar de múltiples sentencias (UDF escalar) en la base de datos AdventureWorks2025. 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.
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 AS INT;
SELECT @ret = SUM(p.Quantity)
FROM Production.ProductInventory AS 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.
SELECT ProductModelID,
Name,
dbo.ufnGetInventoryStock(ProductID) AS CurrentSupply
FROM Production.Product
WHERE ProductModelID BETWEEN 75 AND 80;
Para más información y ejemplos de funciones escalares, consulte CREATE FUNCTION.
Ejemplos de función con valores de tabla
Función insertada con valores de tabla (TVF)
El siguiente ejemplo crea una función en línea con valores de tabla (TVF) en la base de datos AdventureWorks2025. 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.
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
INNER JOIN Sales.SalesOrderDetail AS SD
ON SD.ProductID = P.ProductID
INNER JOIN Sales.SalesOrderHeader AS SH
ON SH.SalesOrderID = SD.SalesOrderID
INNER 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);
Función con valores de tabla de múltiples instrucciones (MSTVF)
El siguiente ejemplo crea una función con valores de tabla de varias sentencias (MSTVF) en la base de datos AdventureWorks2025. 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.
IF OBJECT_ID(N'dbo.ufn_FindReports', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INT)
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 (
-- Get the initial list of Employees for Manager n
SELECT e.BusinessEntityID,
e.OrganizationNode,
p.FirstName,
p.LastName,
e.JobTitle,
0
FROM HumanResources.Employee AS e
INNER JOIN Person.Person AS 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 AS e
INNER JOIN EMP_cte
ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
INNER JOIN Person.Person AS 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.
SELECT EmployeeID,
FirstName,
LastName,
JobTitle,
RecursionLevel
FROM dbo.ufn_FindReports(1);
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), consulte CREATE FUNCTION.
procedimientos recomendados
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. 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:
Especifique la cláusula
WITH SCHEMABINDINGcuando vaya a crear la 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.Ejecute el procedimiento almacenado sp_refreshsqlmodule después de modificar cualquier objeto que se especifique en la definición de la UDF.
Si crea una UDF que no tiene acceso a los datos, especifique la opción SCHEMABINDING para evitar que el optimizador de consultas genere operadores de cola de impresión innecesarios para planes de consulta que impliquen estas UDF. Para obtener más información sobre las colas de impresión, vea Referencia de operador de plan de presentación lógico y físico. Para obtener más información sobre la creación de una función enlazada a un esquema, vea Funciones enlazadas a esquema.
Es posible unirse a una MSTVF en una cláusula FROM, pero puede provocar un rendimiento incorrecto. SQL 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. Para lograr el mejor rendimiento, siempre que sea posible, use combinaciones entre las tablas base en lugar de funciones.
Las MSTVF tienen una estimación de cardinalidad fija de 100 a partir de SQL Server 2014 (12.x) y de 1 para versiones anteriores de SQL Server.
En SQL Server 2017 (14.x) y versiones posteriores, la optimización de un plan de ejecución que usa MSTVF puede usar la ejecución intercalada, lo que da como resultado el uso de la cardinalidad real en lugar de la heurística mencionada anteriormente.
Para obtener más información, vea Ejecución intercalada de funciones con valores de tabla de múltiples instrucciones.
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. 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.