• Article

Types de fonctions

SQL Server 2008 prend en charge les fonctions définies par l'utilisateur, ainsi que les fonctions système intégrées.

Fonctions scalaires

Les fonctions scalaires définies par l'utilisateur retournent une valeur de donnée unique dont le type est défini dans la clause RETURNS. Une fonction scalaire incluse ne contient pas de corps ; la valeur scalaire est le résultat d'une instruction unique. Le corps d'une fonction scalaire à instructions multiples, défini dans un bloc BEGIN...END, contient une série d'instructions Transact-SQL qui retournent la valeur unique. Le type de retour peut être n'importe quel type de données à l'exception de text, ntext, image, cursor et timestamp.

L'exemple suivant illustre la création d'une fonction scalaire à instructions multiples. À partir d'une valeur d'entrée unique (ProductID), la fonction retourne une valeur de donnée unique, en l'occurrence, la quantité totale du produit spécifié en stock.

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

Dans l'exemple suivant, la fonction ufnGetInventoryStock est utilisée pour déterminer la quantité en stock des produits dont la valeur ProductModelID est comprise entre 75 et 80.

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

Fonctions table

Les fonctions table définies par l'utilisateur retournent un type de données table. Une fonction table incluse ne contient pas de corps ; la table est le jeu de résultats d'une instruction SELECT unique.

L'exemple suivant illustre la création d'une fonction table incluse. À partir d'un paramètre d'entrée unique (storeID), la fonction retourne les colonnes ProductID, Name, ainsi que le total cumulé des ventes (YTD Total) pour chaque produit vendu au magasin du client.

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

L'exemple suivant appelle la fonction et désigne le client 602.

SELECT * FROM Sales.ufn_SalesByStore (602);

Dans le cas d'une fonction table à instructions multiples, le corps, défini dans un bloc BEGIN...END, contient une série d'instructions Transact-SQL qui génèrent et insèrent des lignes dans la table appelée à être retournée.

L'exemple suivant illustre la création d'une fonction table. À partir d'un paramètre d'entrée unique (EmployeeID), la fonction retourne la liste de tous les employés qui sont sous la responsabilité directe ou indirecte de l'employé spécifié. La fonction est ensuite appelée en spécifiant l'ID d'employé 19.

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

Fonctions intégrées

Les fonctions intégrées sont fournies par SQL Server pour vous aider à effectuer diverses opérations. Elles ne peuvent pas être modifiées. Vous pouvez utiliser des fonctions intégrées dans les instructions Transact-SQL pour :

Les fonctions intégrées retournent des types de données scalaires ou table. Par exemple, @@ERROR retourne 0 si la dernière instruction Transact-SQL s'est exécutée correctement. Si l'instruction a généré une erreur, @@ERROR retourne le numéro de l'erreur. Quant à la fonction SUM(parameter), elle retourne la somme de toutes les valeurs du paramètre.