Créer des fonctions définies par l’utilisateur (moteur de base de données)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Base de données SQL dans Microsoft Fabric

Cet article explique comment créer une fonction définie par l’utilisateur (UDF) dans SQL Server à l’aide de Transact-SQL.

Limites

Les fonctions définies par l’utilisateur ne permettent pas d’exécuter des actions qui modifient l’état des bases de données.

Les fonctions définies par l’utilisateur ne peuvent pas comporter de clause OUTPUT INTO avec une table comme cible.

Les fonctions définies par l’utilisateur ne peuvent pas renvoyer plusieurs jeux de résultats. Utilisez une procédure stockée si vous devez renvoyer plusieurs jeux de résultats.

La gestion des erreurs est restreinte dans une fonction définie par l’utilisateur. Une fonction UDF ne prend pas en charge TRY...CATCH, @ERRORou RAISERROR.

Les fonctions définies par l’utilisateur ne peuvent pas appeler une procédure stockée, mais elles peuvent appeler une procédure stockée étendue.

Les fonctions définies par l’utilisateur ne peuvent pas utiliser des tables SQL dynamiques ou temporaires. Les variables de table sont autorisées.

SET Les instructions ne sont pas autorisées dans une fonction définie par l'utilisateur (par exemple, SET NOCOUNT ON;). L’attribution de valeur de variable peut utiliser SET.

La clause FOR XML n’est pas autorisée.

Fonctions imbriquées définies par l’utilisateur

Les fonctions définies par l’utilisateur peuvent être imbriquées. Autrement dit, une fonction définie par l’utilisateur peut appeler une autre. Le niveau d’imbrication est incrémenté lorsque la fonction appelée commence à s’exécuter, et décrémenté lorsque l’exécution est terminée.

Les fonctions définies par l’utilisateur peuvent être imbriquées jusqu’à 32 niveaux. Le dépassement des niveaux d’imbrication maximum autorisés, provoque l’échec de la totalité de la chaîne de fonctions appelantes. Toute référence à du code managé depuis une fonction Transact-SQL définie par l’utilisateur compte pour un niveau parmi les 32 niveaux d’imbrication possibles.

Les méthodes appelées à partir du code managé ne comptent pas par rapport à cette limite.

Déclarations du Service Broker

Les instructions Service Broker suivantes ne peuvent pas être incluses dans la définition d’une fonction Transact-SQL définie par l’utilisateur :

• BEGIN DIALOG CONVERSATION
• END CONVERSATION
• GET CONVERSATION GROUP
• MOVE CONVERSATION
• RECEIVE
• SEND

Fonctions d’effet secondaire

Les fonctions intégrées non déterministes suivantes ne peuvent pas être utilisées dans une Transact-SQL fonction définie par l’utilisateur (UDF).

• NEWID
• NEWSEQUENTIALID
• RAND
• TEXTPTR

Si vous référencez l’une de ces fonctions à l’intérieur d’une fonction UDF, vous obtenez l’erreur suivante :

Msg 443, Level 16, State 1
Invalid use of a side-effecting operator <operator> within a function.

Pour obtenir la liste des fonctions système intégrées déterministes et non déterministes, consultez Fonctions déterministes et non déterministes.

Pour contourner ce problème, vous pouvez encapsuler la fonction d’effet secondaire dans une vue et appeler la vue à partir d’une fonction.

autorisations

Nécessite l’autorisation CREATE FUNCTION dans la base de données et l’autorisation ALTER sur le schéma dans lequel la fonction est créée. Si la fonction spécifie un type défini par l’utilisateur, elle nécessite l’autorisation EXECUTE sur le type.

Exemples de fonctions scalaires

Fonction scalaire (UDF scalaire)

L’exemple suivant crée une fonction scalaire à plusieurs instructions (scalaire UDF) dans la base de données AdventureWorks2025. À 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.

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 AS INT;
    SELECT @ret = SUM(p.Quantity)
    FROM Production.ProductInventory AS p
    WHERE p.ProductID = @ProductID
          AND p.LocationID = '6';
    IF (@ret IS NULL)
        SET @ret = 0;
    RETURN @ret;
END

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.

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

Pour en savoir plus et obtenir des exemples de fonctions scalaires, consultez CREATE FUNCTION.

Exemples de fonctions table

Fonction table inline (TVF)

L’exemple suivant crée une fonction à valeurs de table en ligne (TVF) dans la base de données AdventureWorks2025. À 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.

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
          INNER JOIN Sales.SalesOrderDetail AS SD
              ON SD.ProductID = P.ProductID
          INNER JOIN Sales.SalesOrderHeader AS SH
              ON SH.SalesOrderID = SD.SalesOrderID
          INNER JOIN Sales.Customer AS C
              ON SH.CustomerID = C.CustomerID
     WHERE C.StoreID = @storeid
     GROUP BY P.ProductID, P.Name
);

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

SELECT *
FROM Sales.ufn_SalesByStore(602);

Fonction table à instructions multiples (MSTVF)

L’exemple suivant crée une fonction à valeurs de table multi-instructions (MSTVF) dans la base de données AdventureWorks2025. À 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.

IF OBJECT_ID(N'dbo.ufn_FindReports', N'TF') IS NOT NULL
    DROP FUNCTION dbo.ufn_FindReports;
GO

CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INT)
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 (
        -- Get the initial list of Employees for Manager n
        SELECT e.BusinessEntityID,
               e.OrganizationNode,
               p.FirstName,
               p.LastName,
               e.JobTitle,
               0
        FROM HumanResources.Employee AS e
             INNER JOIN Person.Person AS 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 AS e
             INNER JOIN EMP_cte
                 ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
             INNER JOIN Person.Person AS 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

L’exemple suivant appelle la fonction et spécifie l’ID d’employé 1.

SELECT EmployeeID,
       FirstName,
       LastName,
       JobTitle,
       RecursionLevel
FROM dbo.ufn_FindReports(1);

Pour en savoir plus et obtenir des exemples de fonctions table inline (TVF inline) et de fonctions table à instructions multiples, consultez CREATE FUNCTION.

Bonnes pratiques

Si une fonction définie par l’utilisateur (UDF) n’est pas créée avec la clause SCHEMABINDING, les modifications apportées aux objets sous-jacents peuvent affecter la définition de la fonction et produire des résultats inattendus en cas d’appel. Nous vous recommandons d’implémenter l’une des méthodes suivantes pour vous assurer que la fonction ne devient pas obsolète en raison des modifications apportées à ses objets sous-jacents :

• Spécifiez la clause WITH SCHEMABINDING lorsque vous créez l’UDF. Ainsi, les objets référencés dans la définition de la fonction ne peuvent pas être modifiés sauf si la fonction est également modifiée.

• Exécutez la procédure stockée sp_refreshsqlmodule après avoir modifié tout objet spécifié dans la définition de la fonction UDF.

Si vous créez une UDF qui n’accède pas aux données, spécifiez l’option SCHEMABINDING pour empêcher l’optimiseur de requête de générer des opérateurs de spool inutiles pour les plans de requête impliquant cette UDF. Pour en savoir plus sur les spools, consultez Référence des opérateurs logiques et physiques de plan d’exécution de requêtes. Pour plus d’informations sur la création d’une fonction liée à un schéma, consultez Fonctions liées à un schéma.

Il est possible de se joindre à une fonction table à instructions multiples dans une clause FROM, mais cela peut nuire aux performances. SQL Server ne peut pas employer toutes les techniques optimisées sur certaines instructions pouvant être incluses dans une MSTVF. Par conséquent, le plan de requête obtenu n’est pas optimal. Pour obtenir de meilleures performances, quand cela est possible, utilisez des jointures entre les tables de base au lieu des fonctions.

Les MSTVF ont une estimation de cardinalité fixe de 100 à partir de SQL Server 2014 (12.x) et 1 pour les versions antérieures de SQL Server.

Dans SQL Server 2017 (14.x) et versions ultérieures, l’optimisation d’un plan d’exécution qui utilise des fichiers MSTVF peut utiliser l’exécution entrelacée, ce qui entraîne l’utilisation de la cardinalité réelle au lieu des heuristiques mentionnées précédemment.

Pour plus d’informations, consultez Exécution entrelacée pour les fonctions table à instructions multiples.

ANSI_WARNINGS n’est pas honoré lorsque vous transmettez des paramètres dans une procédure stockée, dans une fonction définie par l’utilisateur ou lorsque vous déclarez et définissez des variables dans une instruction par lot. Par exemple, si une variable est définie comme char(3) et qu’une valeur de plus de trois caractères lui est attribuée, les données sont tronquées en fonction de la taille définie, et l’instruction INSERT ou UPDATE réussit.

Contenu associé

• Fonctions définies par l’utilisateur
• CRÉER FONCTION (Transact-SQL)
• MODIFIER FONCTION (Transact-SQL)
• FONCTION DE CHUTE (Transact-SQL)
• FONCTION DE SUPPRESSION DE PARTITION (Transact-SQL)