Observação
O acesso a essa página exige autorização. Você pode tentar entrar ou alterar diretórios.
O acesso a essa página exige autorização. Você pode tentar alterar os diretórios.
Aplica-se a:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSistema de Plataforma de Análise (PDW)Banco de dados SQL no Microsoft Fabric
Este artigo descreve como criar uma UDF (função definida pelo usuário) no SQL Server usando o Transact-SQL.
Limitações
Funções definidas pelo usuário não podem ser usadas para executar ações que modificam o estado do banco de dados.
As funções definidas pelo usuário não podem conter uma cláusula OUTPUT INTO que tenha uma tabela como seu destino.
As funções definidas pelo usuário não podem retornar vários conjuntos de resultados. Use um procedimento armazenado se precisar retornar vários conjuntos de resultados.
O tratamento de erros é restringido em uma função definida pelo usuário. Uma UDF não oferece suporte a TRY...CATCH, @ERROR ou RAISERROR.
As funções definidas pelo usuário não podem chamar um procedimento armazenado, mas podem chamar um procedimento armazenado estendido.
As funções definidas pelo usuário não podem fazer uso de SQL dinâmico ou tabelas temporárias. Variáveis de tabela são permitidas.
Instruções SET não são permitidas em uma função definida pelo usuário (por exemplo, SET NOCOUNT ON;). A atribuição de valor variável pode usar SET.
A cláusula FOR XML não é permitida.
Funções aninhadas definidas pelo usuário
As funções definidas pelo usuário podem ser aninhadas. Ou seja, uma função definida pelo usuário pode chamar outra. 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.
Até 32 níveis de funções definidas pelo usuário podem ser aninhados. Se o máximo de níveis de aninhamento for excedido, ocorrerá uma falha em toda a cadeia de funções da chamada de aninhamento. 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 aninhamento de nível 32.
Os métodos invocados a partir do código gerenciado não são contados em relação a esse limite.
Instruções do Service Broker
As seguintes instruções do Service Broker não podem ser incluídas na definição de uma função Transact-SQL definida pelo usuário:
BEGIN DIALOG CONVERSATIONEND CONVERSATIONGET CONVERSATION GROUPMOVE CONVERSATIONRECEIVESEND
Funções de efeito colateral
As funções internas não determinísticas a seguir não podem ser usadas em uma UDF (função definida pelo usuário) Transact-SQL.
NEWIDNEWSEQUENTIALIDRANDTEXTPTR
Se você fizer referência a uma dessas funções dentro de uma UDF, receberá o seguinte erro:
Msg 443, Level 16, State 1
Invalid use of a side-effecting operator <operator> within a function.
Para obter uma lista das funções internas do sistema determinísticas e não determinísticas, consulte Funções determinísticas e não determinísticas.
Para contornar esse problema, você pode envolver a função de efeito colateral em uma exibição e chamar a exibição de dentro de uma função.
Permissões
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. Se a função especificar um tipo definido pelo usuário, a permissão EXECUTE será exigida no tipo.
Exemplos de função escalar
Função escalar (UDF escalar)
O exemplo a seguir cria uma função escalar de múltiplas instruções (UDF escalar) no banco de dados AdventureWorks2025. 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.
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
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.
SELECT ProductModelID,
Name,
dbo.ufnGetInventoryStock(ProductID) AS CurrentSupply
FROM Production.Product
WHERE ProductModelID BETWEEN 75 AND 80;
Para obter mais informações e exemplos de funções escalares, consulte CREATE FUNCTION.
Exemplos de função com valor de tabela
TVF embutida
O exemplo a seguir cria uma função de valores em tabela (TVF) em linha no banco de dados AdventureWorks2025. 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.
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
);
O exemplo a seguir invoca a função e especifica a ID do cliente 602.
SELECT *
FROM Sales.ufn_SalesByStore(602);
MSTVF (Função com valor de tabela de várias instruções)
O exemplo a seguir cria uma função de tabela com múltiplas instruções (MSTVF) no banco de dados AdventureWorks2025. 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. A função que especifica a ID do funcionário 109 é invocada em seguida.
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
O exemplo a seguir invoca a função e especifica a ID do funcionário 1.
SELECT EmployeeID,
FirstName,
LastName,
JobTitle,
RecursionLevel
FROM dbo.ufn_FindReports(1);
Para obter mais informações e exemplos de TVFs embutidas e MSTVFs (funções com valor de tabela de várias instruções), veja CREATE FUNCTION.
Práticas recomendadas
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. É 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:
Especifique a cláusula
WITH SCHEMABINDINGquando você estiver criando a 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.Execute o procedimento armazenado sp_refreshsqlmodule depois de modificar um objeto especificado na definição da UDF.
Se estiver criando uma UDF que não acessa dados, especifique a opção SCHEMABINDING para impedir que o otimizador de consulta gere operadores de spool desnecessários para planos de consulta que envolvem essas UDFs. Para obter mais informações sobre spools, consulte Referência de operador de plano de execução lógico e físico. Para obter mais informações sobre como criar uma função associada a esquema, confira Funções associadas a esquema.
O ingresso em uma MSTVF em uma cláusula FROM é possível, mas pode resultar em um baixo desempenho. O SQL 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. Para obter o melhor desempenho possível, sempre que possível use junções entre tabelas base em vez de funções.
Os MSTVFs têm uma estimativa de cardinalidade fixa de 100 a partir do SQL Server 2014 (12.x) e 1 para versões anteriores do SQL Server.
No SQL Server 2017 (14.x) e versões posteriores, otimizar um plano de execução que usa MSTVFs pode usar a execução intercalada, o que resulta no uso da cardinalidade real em vez da heurística mencionada anteriormente.
Para obter mais informações, veja Execução intercalada para funções com valor de tabela de várias instruções.
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. 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.