Compartilhar via

Criar funções definidas pelo usuário (Mecanismo de Banco de Dados)

Este tópico descreve como criar uma função definida pelo usuário no SQL Server usando o Transact-SQL.

Nesse Tópico

Antes de começar

Limitações e restriçõ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 cláusula OUTPUT INTO 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 dá 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 usar tabelas temporárias ou SQL dinâmicas. Variáveis de tabela são permitidas.

  • Instruções SET não são permitidas em uma função definida pelo usuário.

  • A cláusula FOR XML não é permitida

  • 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 decrementado quando a função chamada termina a execução. As funções definidas pelo usuário podem ser aninhadas até 32 níveis. Exceder os níveis máximos de aninhamento faz com que toda a cadeia de funções de chamada falhe. Qualquer referência ao código gerenciado 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 de dentro do código gerenciado não contam com esse limite.

  • 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-SQL:

    • INICIAR CONVERSA DE DIÁLOGO

    • ENCERRAR CONVERSAÇÃO

    • OBTER GRUPO DE CONVERSAÇÃO

    • Transferir Conversa

    • RECEBER

    • ENVIAR

Segurança

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, exigirá a permissão EXECUTE no tipo.

Funções escalares

O exemplo a seguir cria uma função escalar de vários estados no banco de dados AdventureWorks2012. A função usa um valor de entrada, um ProductIDe 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 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;  
GO

O exemplo a seguir usa a ufnGetInventoryStock função para retornar a quantidade de inventário atual para produtos que têm ProductModelID entre 75 e 80.

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

Table-Valued Funções

O exemplo a seguir cria uma função embutida com valor de tabela no banco de dados AdventureWorks2012. A função usa um parâmetro de entrada, uma ID do cliente (loja), e retorna as colunas ProductID, Name, e a agregação de vendas acumuladas no ano 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   
    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.

SELECT * FROM Sales.ufn_SalesByStore (602);

O exemplo a seguir cria uma função com valor de tabela no banco de dados AdventureWorks2012. 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. Em seguida, a função é invocada especificando a ID 109 do funcionário.

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  
-- Example invocation  
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel  
FROM dbo.ufn_FindReports(1);

Consulte Também

Funções definidas pelo usuário
CREATE FUNCTION (Transact-SQL)

  • Last updated on