Tipos de funções

O SQL Server 2008 oferece suporte para funções definidas pelo usuário e funções internas do sistema.

Funções escalares

As funções escalares definidas pelo usuário retornam um valor único de dados do tipo definido na cláusula RETURNS. Para uma função escalar embutida, não há um corpo de função; o valor escalar é o resultado de uma única instrução. Para uma função escalar 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 o valor único. O tipo de retorno pode ser qualquer tipo de dados, exceto text, ntext, image, cursore timestamp.

Os exemplos a seguir criam uma função escalar de várias instruções. A função pega um valor de entrada, um ProductID, e retorna um único valor de dados, a quantidade agregada do produto especificado no inventário.

USE AdventureWorks2008R2;
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

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

USE AdventureWorks2008R2;
GO
SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply
FROM Production.Product
WHERE ProductModelID BETWEEN 75 and 80;
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 AdventureWorks2008R2;
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 '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
);
GO

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

SELECT * FROM Sales.ufn_SalesByStore (602);

Para 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 na tabela que será retornada.

O exemplo a seguir cria uma função com valor de tabela. A função toma 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.

USE AdventureWorks2008R2;
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,
    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); 

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:

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.

Consulte também

Outros recursos