Types de fonctions

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. Les fonctions scalaires simples n'ont pas de corps de fonction ; la valeur scalaire résulte d'une instruction de fonction unique (souvent une instruction SELECT). Dans les fonctions scalaires à instructions multiples, le corps de la fonction est défini dans un bloc BEGIN...END et contient une série d'instructions Transact-SQL qui retournent une valeur unique. Le type de retour peut correspondre à n'importe quel type de données à l'exception de text, ntext, image, cursor, spatial, hierarchyID et timestamp.

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

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

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 la quantité agrégée du produit spécifié en stock sous forme de valeur de retour unique.

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

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 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

L'exemple suivant appelle la fonction et spécifie l'ID client 602.

SELECT * FROM Sales.ufn_SalesByStore (602);

Dans une fonction table à instructions multiples, un bloc BEGIN...END définit le corps de la fonction et contient une série d'instructions Transact-SQL qui génèrent et insèrent des lignes dans les résultats de la table.

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é ayant l'ID spécifié 109. L'ID d'employé 109 est ensuite utilisé comme paramètre d'entrée dans l'exemple ; par ailleurs, la liste des employés est retournée dans la table de résultats.

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

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 :

• accéder aux informations contenues dans les tables système de SQL Server sans accéder directement aux tables système ; Pour plus d'informations, consultez Utilisation des fonctions système.

• exécuter des tâches usuelles, telles que SUM, GETDATE ou IDENTITY. Pour plus d'informations, consultez Fonctions (Transact-SQL).

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.

Historique des modifications

Voir aussi

Référence

Autres ressources