Nota
O acesso a esta página requer autorização. Pode tentar iniciar sessão ou alterar os diretórios.
O acesso a esta página requer autorização. Pode tentar alterar os diretórios.
Aplica-se a:
Banco de Dados SQL doAzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsAnalytics Platform System (PDW)Banco de Dados SQL no Microsoft Fabric
Este artigo descreve como criar uma função definida pelo usuário (UDF) no SQL Server usando Transact-SQL.
Limitações
As funções definidas pelo usuário não podem ser usadas para executar ações que modifiquem o estado do banco de dados.
As funções definidas pelo usuário não podem conter uma OUTPUT INTO cláusula que tenha uma tabela como 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 é restrito em uma função definida pelo usuário. Uma UDF não suporta TRY...CATCH, @ERRORou 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 tabelas dinâmicas SQL ou temporárias. São permitidas variáveis de tabela.
SET declarações não são permitidas numa função definida pelo utilizador (por exemplo, SET NOCOUNT ON;). A atribuição de valor variável pode usar SET.
A FOR XML cláusula 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 função chamada inicia a execução e diminuído quando a função chamada termina a execução.
As funções definidas pelo usuário podem ser aninhadas em até 32 níveis. Exceder os níveis máximos de aninhamento faz com que toda a cadeia de funções chamadoras falhe. Qualquer referência a código gerenciado a partir de uma função definida pelo usuário Transact-SQL conta como um nível em relação ao limite de aninhamento de 32 níveis.
Os métodos invocados a partir do código gerenciado não contam para esse limite.
Declaraçõ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 seguintes funções internas não determinísticas não podem ser usadas em um Transact-SQL função definida pelo usuário (UDF).
NEWIDNEWSEQUENTIALIDRANDTEXTPTR
Se você fizer referência a uma dessas funções dentro de um UDF, obterá o seguinte erro:
Msg 443, Level 16, State 1
Invalid use of a side-effecting operator <operator> within a function.
Para obter uma lista de funções internas do sistema determinísticas e não determinísticas, consulte Funções determinísticas e não determinísticas.
Para contornar este problema, pode-se encapsular a função de efeito colateral numa vista e chamar a vista de dentro de uma função.
Permissões
Requer CREATE FUNCTION permissão no banco de dados e ALTER permissão no esquema no qual a função está sendo criada. Se a função especificar um tipo definido pelo utilizador, exige EXECUTE permissão para o tipo.
Exemplos de funções escalares
Função escalar (UDF - função definida pelo utilizador escalar)
O exemplo seguinte cria uma função escalar de múltiplas instruções (UDF escalar) na base de dados AdventureWorks2025. A função usa um valor de entrada, um ProductID, e retorna um único valor de dados, a quantidade agregada do produto especificado no inventário.
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 ufnGetInventoryStock função para retornar a quantidade de estoque atual para produtos que têm entre ProductModelID 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ções com valor de tabela
Função com valor de tabela embutido (TVF)
O exemplo seguinte cria uma função com valores de tabela inline (TVF) na base de dados AdventureWorks2025. A função utiliza um parâmetro de entrada, um ID de loja (cliente), e retorna as colunas ProductID, Name e o agregado de vendas do ano até à data presente como YTD Total para cada produto vendido à 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);
Função com valor de tabela de várias instruções (MSTVF)
O exemplo seguinte cria uma função de tabela com múltiplas instruções (MSTVF) na base 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 se reportam ao funcionário especificado direta ou indiretamente. A função é então invocada especificando o ID do funcionário 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
O exemplo a seguir invoca a função e especifica o ID do funcionário 1.
SELECT EmployeeID,
FirstName,
LastName,
JobTitle,
RecursionLevel
FROM dbo.ufn_FindReports(1);
Para obter mais informações e exemplos de funções com valor de tabela embutido (TVFs embutidos) e funções com valor de tabela de várias instruções (MSTVFs), consulte CREATE FUNCTION.
Melhores práticas
Se uma função definida pelo usuário (UDF) não for criada com a cláusula, as SCHEMABINDING alterações feitas nos objetos subjacentes podem afetar a definição da função e produzir resultados inesperados quando ela for invocada. Recomendamos que você implemente um dos seguintes métodos para garantir que a função não fique desatualizada devido a alterações em seus objetos subjacentes:
Especifique a
WITH SCHEMABINDINGcláusula ao criar o UDF. Isso garante que os objetos referenciados na definição de função não possam ser modificados, a menos que a função também seja modificada.Execute o sp_refreshsqlmodule procedimento armazenado depois de modificar qualquer objeto especificado na definição do UDF.
Se estiver criando um UDF que não acesse dados, especifique a SCHEMABINDING opção para impedir que o otimizador de consulta gere operadores de spool desnecessários para planos de consulta envolvendo esses UDFs. Para obter mais informações sobre bobinas, consulte Referência de operador de showplan lógico e físico. Para obter mais informações sobre como criar uma função ligada ao esquema, consulte Funções vinculadas ao esquema.
Aderir a um MSTVF em uma FROM cláusula é possível, mas pode resultar em baixo desempenho. O SQL Server não consegue usar todas as técnicas otimizadas em algumas instruções que podem ser incluídas em um MSTVF, resultando em um plano de consulta subótimo. Para obter o melhor desempenho possível, sempre que possível use junções entre tabelas base em vez de funções.
MSTVFs têm uma estimativa de cardinalidade fixa de 100 a partir do SQL Server 2014 (12.x) e de 1 para versões anteriores do SQL Server.
No SQL Server 2017 (14.x) e versões posteriores, a otimização de 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, consulte Execução intercalada para funções com valor de tabela de várias instruções.
ANSI_WARNINGS não é honrado quando você passa parâmetros em um procedimento armazenado, função definida pelo usuário ou quando declara e define variáveis em uma instrução batch. Por exemplo, se uma variável for definida como char(3) e, em seguida, definida como um valor maior que três caracteres, os dados serão truncados para o tamanho definido e a INSERT instrução or UPDATE terá êxito.