Criar funções definidas pelo usuário (Mecanismo de Banco de Dados)Create User-defined Functions (Database Engine)

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure nãoAzure Synapse Analytics (SQL DW) nãoParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Este tópico descreve como criar uma UDF (função definida pelo usuário) no SQL ServerSQL Server usando 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 começarBefore You Begin

Limitações e restriçõesLimitations and restrictions

  • Funções definidas pelo usuário não podem ser usadas para executar ações que modificam o estado do banco de dados.User-defined functions cannot be used to perform actions that modify the database state.

  • As funções definidas pelo usuário não podem conter uma cláusula OUTPUT INTO que tenha uma tabela como seu destino.User-defined functions cannot contain an OUTPUT INTO clause that has a table as its target.

  • As funções definidas pelo usuário não podem retornar vários conjuntos de resultados.User-defined functions can not return multiple result sets. Use um procedimento armazenado se precisar retornar vários conjuntos de resultados.Use a stored procedure if you need to return multiple result sets.

  • O tratamento de erros é restringido em uma função definida pelo usuário.Error handling is restricted in a user-defined function. Uma UDF não é compatível com TRY...CATCH, @ERROR ou RAISERROR.A UDF does not support TRY...CATCH, @ERROR or RAISERROR.

  • As funções definidas pelo usuário não podem chamar um procedimento armazenado, mas podem chamar um procedimento armazenado estendido.User-defined functions cannot call a stored procedure, but can call an extended stored procedure.

  • As funções definidas pelo usuário não podem fazer uso de SQL dinâmico ou tabelas temporárias.User-defined functions cannot make use of dynamic SQL or temp tables. Variáveis de tabela são permitidas.Table variables are allowed.

  • As instruções SET não são permitidas em uma função definida pelo usuário.SET statements are not allowed in a user-defined function.

  • A cláusula FOR XML vazia não é permitida.The FOR XML clause is not allowed.

  • Funções definidas pelo usuário podem ser aninhadas, isto é, uma função definida pelo usuário pode chamar outra.User-defined functions can be nested; that is, one user-defined function can call another. O nível de aninhamento é incrementado quando a execução da função é iniciada, e reduzido quando a execução da função chamada é concluída.The nesting level is incremented when the called function starts execution, and decremented when the called function finishes execution. Até 32 níveis de funções definidas pelo usuário podem ser aninhados.User-defined functions can be nested up to 32 levels. Se o máximo de níveis de aninhamento for excedido haverá falha em toda a cadeia de funções da chamada de aninhamento.Exceeding the maximum levels of nesting causes the whole calling function chain to fail. Qualquer referência a um código gerenciado de uma função definida pelo usuário do Transact-SQL é contada como um nível em relação ao limite de 32 níveis de aninhamento.Any reference to managed code from a Transact-SQL user-defined function counts as one level against the 32-level nesting limit. Os métodos invocados a partir do código gerenciado não são contados em relação a esse limite.Methods invoked from within managed code do not count against this limit.

  • As seguintes instruções do Service Broker não podem ser incluídas na definição de uma função definida pelo usuário Transact-SQLTransact-SQL: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

PermissõesPermissions

Requer a permissão CREATE FUNCTION no banco de dados e a permissão ALTER no esquema no qual a função está sendo criada.Requires CREATE FUNCTION permission in the database and ALTER permission on the schema in which the function is being created. Se a função especificar um tipo definido pelo usuário, a permissão EXECUTE será exigida no tipo.If the function specifies a user-defined type, requires EXECUTE permission on the type.

Funções escalaresScalar Functions

O exemplo a seguir cria uma função escalar (UDF escalar) de várias instruções no banco de dados AdventureWorks2012AdventureWorks2012.The following example creates a multi-statement scalar function (scalar UDF) in the AdventureWorks2012AdventureWorks2012 database. A função pega um valor de entrada, um ProductID, e retorna um único valor de dados, a quantidade agregada do produto especificado no estoque.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; 

O exemplo a seguir usa a função ufnGetInventoryStock , para retornar a quantidade atual do estoque dos produtos que têm um ProductModelID entre 75 e 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;  

Observação

Para obter mais informações e exemplos de funções escalares, confira CREATE FUNCTION (Transact-SQL).For more information and examples of scalar functions, see CREATE FUNCTION (Transact-SQL).

Funções com valor de tabelaTable-Valued Functions

O exemplo a seguir cria uma TVF (função com valor de tabela) embutida no banco de dados AdventureWorks2012AdventureWorks2012.The following example creates an inline table-valued function (TVF) in the AdventureWorks2012AdventureWorks2012 database. A função pega um parâmetro de entrada, um ID cliente (loja), e retorna as colunas ProductID, Namee a agregação das vendas do ano, até a data atual, como YTD Total para cada produto vendido para a loja.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  
);  

O exemplo a seguir invoca a função e especifica a ID do cliente 602.The following example invokes the function and specifies customer ID 602.

SELECT * FROM Sales.ufn_SalesByStore (602);  

O exemplo a seguir cria uma MSTVF (função com valor de tabela de várias instruções) no banco de dados AdventureWorks2012AdventureWorks2012.The following example creates a multi-statement table-valued function (MSTVF) in the AdventureWorks2012AdventureWorks2012 database. A função usa um único parâmetro de entrada, um EmployeeID , e retorna uma lista de todos os funcionários que reportam direta ou indiretamente ao funcionário 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. A função que especifica a ID do funcionário 109 é invocada em seguida.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  

O exemplo a seguir invoca a função e especifica a ID do funcionário 1.The following example invokes the function and specifies employee ID 1.

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

Observação

Para obter mais informações e exemplos TVFs embutidas (funções com valor de tabela embutidas) e MSTVFs (funções com valor de tabela de várias instruções), confira 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áticas recomendadasBest Practices

Se uma UDF (função definida pelo usuário) não for criada com a cláusula SCHEMABINDING, as alterações feitas nos objetos subjacentes poderão afetar a definição da função e produzir resultados inesperados quando ela for chamada.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. É recomendável que você implemente um dos seguintes métodos para garantir que a função não se torne desatualizada devido a alterações em seus objetos subjacentes: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 a cláusula WITH SCHEMABINDING quando você estiver criando a UDF.Specify the WITH SCHEMABINDING clause when you are creating the UDF. Isso garante que os objetos referenciados na definição da função não possam ser modificados, a menos que a função também seja modificada.This ensures that the objects referenced in the function definition cannot be modified unless the function is also modified.

  • Execute o procedimento armazenado sp_refreshsqlmodule depois de modificar um objeto especificado na definição da UDF.Execute the sp_refreshsqlmodule stored procedure after modifying any object that is specified in the definition of the UDF.

Se estiver criando uma UDF que não acessa os dados, especifique a opção SCHEMABINDING.If creating a UDF that does not access data, specify the SCHEMABINDING option. Isso impedirá que o otimizador de consulta gere operadores de spool desnecessários para planos de consulta que envolvem essas UDFs.This will prevent the query optimizer from generating unnecessary spool operators for query plans involving these UDFs. Para obter mais informações sobre spools, confira Referência de operadores lógicos e físicos de plano de execução.For more information on spools, see Showplan Logical and Physical Operators Reference. Para obter mais informações sobre como criar uma função associada a esquema, confira Funções associadas a esquema.For more information on creating a schema bound function, see Schema-bound functions.

O ingresso em uma MSTVF em uma cláusula FROM é possível, mas pode resultar em baixo desempenho.Joining to an MSTVF in a FROM clause is possible, but can result in poor performance. SQL ServerSQL Server não pode usar todas as técnicas otimizadas em algumas instruções que podem ser incluídas em uma MSTVF, resultando em um plano de consulta de qualidade inferior.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 obter o melhor desempenho possível, sempre que possível use junções entre tabelas base em vez de funções.To obtain the best possible performance, whenever possible use joins between base tables instead of functions.

Importante

As MSTVFs têm uma estimativa de cardinalidade fixa de 100 começando com SQL Server 2014 (12.x)SQL Server 2014 (12.x) e de 1 para versões anteriores 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.
Começando com SQL Server 2017 (14.x)SQL Server 2017 (14.x), otimizar um plano de execução que usa as MSTVFs pode aproveitar a execução intercalada, que resulta no uso de cardinalidade real em vez da heurística acima.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 obter mais informações, consulte Interleaved execution for multi-statement table valued functions (Execução intercalada para funções com valor de tabela de várias instruções).For more information, see Interleaved execution for multi-statement table valued functions.

Observação

ANSI_WARNINGS não é cumprido quando você passa parâmetros em um procedimento armazenado, em uma função definida pelo usuário ou quando declara ou define variáveis em uma instrução de lote.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 exemplo, se a variável for definida como char(3) e, em seguida, configurada com um valor maior que três caracteres, os dados serão truncados até o tamanho definido e a instrução INSERT ou UPDATE terá êxito.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 TambémSee Also

Funções definidas pelo usuário 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)
Veja mais exemplos na comunidadeMore examples in the community