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:
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 internas (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.