Tipos de funções

Funções Escalares

As funções escalares definidas pelo usuário retornam um valor único de dados do tipo definido na cláusula RETURNS. Funções escalares simples não têm nenhum corpo de função; o valor escalar é o resultado de uma única instrução de função (freqüentemente uma instrução SELECT). Em funções escalares de várias instruções, o corpo da função é definido em um bloco BEGIN...END, contém uma série de instruções Transact-SQL que retornam um valor único. O tipo de retorno pode ser qualquer tipo de dados, exceto text, ntext, image, cursor, spatial, hierarchyID e timestamp.

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

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

O exemplo a seguir cria uma função escalar de várias instruções. A função usa um valor de entrada, um ProductID e retorna a quantidade agregada do produto especificado no inventário como o único valor de retorno.

USE AdventureWorks;
GO
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

Funções com valor de tabela

As funções com valor de tabela definidas pelo usuário retornam um tipo de dados table. Para uma função com valor de tabela embutida, não há um corpo de função; a tabela é o conjunto de resultados de uma única instrução SELECT.

O exemplo a seguir cria uma função com valor de tabela embutida. A função pega um parâmetro de entrada, um ID cliente (loja), e retorna as colunas ProductID, Name e a agregação das vendas do ano, até a data atual, como YTD Total para cada produto vendido para a loja.

USE AdventureWorks;
GO
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 'YTD 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
    WHERE SH.CustomerID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

O exemplo a seguir invoca a função e especifica a ID do cliente 602.

SELECT * FROM Sales.ufn_SalesByStore (602);

Em uma função com valor de tabela de várias instruções, o corpo da função definido em um bloco BEGIN...END contém uma série de instruções Transact-SQL que criam e inserem linhas nos resultados da tabela.

O exemplo a seguir cria uma função com valor de tabela. A função usa um único parâmetro de entrada EmployeeID e retorna uma lista de todos os funcionários que se reportam direta ou indiretamente ao funcionário com ID 109 especificado. A ID 109 é então é usada como o parâmetro de entrada no exemplo e uma lista de funcionários é retornada na tabela de resultados.

USE AdventureWorks;
GO
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,
    Name nvarchar(255) NOT NULL,
    Title nvarchar(50) NOT NULL,
    EmployeeLevel int NOT NULL,
    Sort nvarchar (255) NOT NULL
)
--Returns a result set that lists all the employees who report to the 
--specific employee directly or indirectly.*/
AS
BEGIN
   WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS
    (SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)
     FROM HumanResources.Employee AS e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
     WHERE e.EmployeeID = @InEmpID
   UNION ALL
     SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + 
                 LastName)
     FROM HumanResources.Employee as e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID
          JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
-- copy the required columns to the result of the function 
   INSERT @retFindReports
   SELECT EmployeeID, Name, Title, EmployeeLevel, Sort
   FROM DirectReports 
   RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM dbo.ufn_FindReports(109)
ORDER BY Sort;
GO

Funções internas

As funções internas são fornecidas pelo SQL Server para ajudar a executar várias operações. Elas não podem ser modificadas. Você pode usar funções internas em instruções Transact-SQL para:

• Acessar as informações das tabelas do sistema SQL Server, sem acessar as tabelas do sistema diretamente. Para obter mais informações, consulte Usando as funções do sistema.

• Executar tarefas comuns como SUM, GETDATE ou IDENTITY. Para obter mais informações, consulte Funções [Transact-SQL].

As funções internas retornam tipos de dados escalares ou table. Por exemplo, o @ @ Erro retorna 0, se a última instrução Transact-SQL foi executada com êxito. Se uma instrução gerou um erro, o @ @ Erro retornará o número do erro. E a função SUM (parameter) retorna a soma de todos os valores para o parâmetro.

Histórico de alterações

Consulte também

Referência

Outros recursos