Exécution de fonctions définies par l'utilisateur (Moteur de base de données)

Mis à jour : 14 avril 2006

Les fonctions définies par l'utilisateur peuvent être appelées à partir d'une requête ou d'autres instructions ou expressions comme les colonnes calculées ou les expressions de chaîne. Les fonctions à valeurs scalaires peuvent être exécutées à l'aide de l'instruction EXECUTE.

Appel de fonctions définies par l'utilisateur retournant une valeur scalaire

Vous pouvez appeler une fonction définie par l'utilisateur qui retourne une valeur scalaire partout où une expression scalaire de type de données identique est autorisée dans les instructions Transact-SQL. Les fonctions scalaires doivent être appelées en utilisant au moins le nom en deux parties de la fonction. Pour plus d'informations sur les noms en plusieurs parties, consultez Conventions de syntaxe de Transact-SQL (Transact-SQL).

Requêtes

Les fonctions définies par l'utilisateur qui retournent des valeurs scalaires sont autorisées dans les emplacements suivants :

• en tant que expression dans select_list (liste de sélection) d'une instruction SELECT ;

  USE AdventureWorks;
  GO
  SELECT ProductID, ListPrice, dbo.ufnGetProductDealerPrice(ProductID, StartDate) AS DealerPrice,
      StartDate, EndDate
  FROM Production.ProductListPriceHistory
  WHERE ListPrice > .0000
  ORDER BY ProductID, StartDate;
  GO
  

• en tant que expression ou string_expression (expression chaîne) dans le prédicat de la clause WHERE ou HAVING ;

  USE AdventureWorks;
  GO
  SELECT ProductID, ListPrice, StartDate, EndDate
  FROM Production.ProductListPriceHistory
  WHERE dbo.ufnGetProductDealerPrice(ProductID, StartDate) > .0000
  ORDER BY ProductID, StartDate;
  GO
  

• en tant que group_by_expression (groupement par expression) dans une clause GROUP BY ;

• en tant que order_by_expression (ordre par expression) dans une clause ORDER BY ;

• en tant que expression dans la clause SET d'une instruction UPDATE ;

  USE AdventureWorks;
  GO
  UPDATE Production.ProductListPriceHistory
  SET ListPrice = dbo.ufnGetProductDealerPrice(ProductID, StartDate)
  WHERE ProductID > 900;
  GO
  

• en tant que expression dans la clause VALUES d'une instruction INSERT.
  Les fonctions définies par l'utilisateur référencées dans ces emplacements sont exécutées logiquement une fois par ligne.

Contraintes CHECK

Une fonction définie par l'utilisateur qui retourne des valeurs scalaires peut être appelée dans des contraintes CHECK si les valeurs d'arguments qui lui sont transmises référencent uniquement des colonnes de la table ou des constantes. Chaque fois que le processeur de requêtes vérifie la contrainte, il appelle la fonction à partir des valeurs d'arguments associées à la ligne en cours de vérification. Le propriétaire d'une table doit également être celui de la fonction définie par l'utilisateur appelée par une contrainte CHECK sur cette table.

Définitions DEFAULT

Les fonctions définies par l'utilisateur peuvent être appelées en tant que constant_expression (expression constante) des définitions DEFAULT si les valeurs d'arguments qui lui sont transmises ne contiennent que des constantes. Le propriétaire de la table doit également être celui de la fonction définie par l'utilisateur appelée par une définition DEFAULT sur cette table.

Colonnes calculées

Une fonction peut être appelée par des colonnes calculées si les valeurs d'arguments qui lui sont transmises référencent uniquement des colonnes de la table ou des constantes. Le propriétaire de la table doit également être celui de la fonction définie par l'utilisateur appelée par une colonne calculée dans cette table.

Opérateurs d'affectation

Les opérateurs d'affectation (left_operand = right_operand) peuvent appeler des fonctions définies par l'utilisateur retournant une valeur scalaire dans l'expression spécifiée en tant qu'opérande droit.

Instructions de contrôle de flux

Les fonctions définies par l'utilisateur qui retournent des valeurs scalaires peuvent être appelées par des instructions de contrôle de flux dans leurs expressions booléennes.

Expressions CASE

Les fonctions définies par l'utilisateur qui retournent une valeur scalaire peuvent être appelées dans n'importe quelle expression CASE.

Instructions PRINT

Les fonctions définies par l'utilisateur qui retournent une chaîne de caractères peuvent être appelées en tant qu'expression string_expr des instructions PRINT.

Fonctions et procédures stockées

• Les arguments de fonction peuvent également faire référence à une fonction définie par l'utilisateur qui retourne une valeur scalaire.
• Les instructions RETURN integer_expression dans les procédures stockées peuvent appeler des fonctions définies par l'utilisateur qui retournent une valeur de type integer (entier) en tant que integer_expression.
• Les instructions RETURN return_type_spec dans les fonctions définies par l'utilisateur peuvent appeler des fonctions utilisateur retournant un type de données scalaire tel que return_type_spec, pour autant que la valeur retournée par la fonction utilisateur appelée puisse être implicitement convertie dans le type de données retourné de la fonction appelante.

Exécution de fonctions définies par l'utilisateur retournant une valeur scalaire

Vous pouvez exécuter des fonctions définies par l'utilisateur retournant des valeurs scalaires de la même manière que des procédures stockées. Lors de l'exécution d'une fonction définie par l'utilisateur retournant une valeur scalaire, les paramètres sont spécifiés comme ils le sont pour les procédures stockées :

• Les valeurs d'arguments ne figurent pas entre parenthèses.
• Les noms de paramètres peuvent être spécifiés.
• Si les noms de paramètres sont spécifiés, les valeurs d'arguments n'ont pas besoin de figurer dans la même séquence que les paramètres.

L'exemple suivant crée une fonction définie par l'utilisateur et retournant une valeur scalaire décimale.

IF OBJECT_ID(N'dbo.ufn_CubicVolume', N'FN') IS NOT NULL
    DROP FUNCTION dbo.ufn_CubicVolume;
GO
CREATE FUNCTION dbo.ufn_CubicVolume
-- Input dimensions in centimeters.
   (@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
    @CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
WITH SCHEMABINDING
AS
BEGIN
   RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END;
GO

L'exemple suivant exécute la fonction dbo.ufn_CubicVolume. À l'aide de l'instruction EXECUTE de Transact-SQL, les arguments sont identifiés dans un ordre différent de celui des paramètres dans la définition de la fonction :

DECLARE @MyDecimalVar decimal(12,3);
EXEC @MyDecimalVar = dbo.ufn_CubicVolume @CubeLength = 12.3,
                        @CubeHeight = 4.5, @CubeWidth = 4.5;
SELECT @MyDecimalVar;
GO

L'exemple suivant exécute la fonction dbo.ufn_CubicVolume sans mention des noms de paramètres :

DECLARE @MyDecimalVar decimal(12,3);
EXEC @MyDecimalVar = dbo.ufn_CubicVolume 12.3, 4.5, 4.5;
SELECT @MyDecimalVar;
GO

Vous pouvez également utiliser la syntaxe ODBC CALL pour exécuter la fonction dbo.ufn_CubicVolume à partir d'applications OLE DB ou ODBC :

-- First use SQLBindParam to bind the return value parameter marker
-- to a program variable of the appropriate type
SQLExecDirect(hstmt,
              "{ CALL ? = dbo.ufn_CubicVolume(12.3, 4.5, 4.5) }",
              SQL_NTS);

Appel de fonctions définies par l'utilisateur retournant un type de données table

Vous pouvez appeler une fonction définie par l'utilisateur retournant une table, là où les expressions de table sont autorisées dans la clause FROM des instructions SELECT, INSERT, UPDATE ou DELETE. L'appel d'une fonction définie par l'utilisateur qui retourne une table peut être suivi d'un alias de table. L'exemple suivant illustre l'appel de la fonction table dbo.ufnGetContactInformation dans la clause FROM d'une instruction SELECT.

USE AdventureWorks;
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(2200);
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(5);
GO

Lorsqu'une fonction définie par l'utilisateur retournant une table est appelée dans la clause FROM d'une sous-requête, les arguments de fonction ne peuvent référencer aucune colonne de la requête externe.

Les curseurs statiques en lecture seule sont le seul type de curseurs pouvant être ouverts par une instruction SELECT dont la clause FROM fait référence à une fonction définie par l'utilisateur qui retourne une table.

Une instruction SELECT qui fait référence à une fonction définie par l'utilisateur retournant une table appelle la fonction une fois.

Appel de fonctions table intégrées

Il existe plusieurs fonctions table intégrées retournant une valeur de type table. L'appel de ces fonctions intégrées définies par l'utilisateur peut être non qualifié ou recourir à l'identificateur de schémas sys. Il est conseillé d'utiliser l'identificateur de schémas sys pour les fonctions table intégrées dans la mesure où il évite les conflits avec les fonctions définies par l'utilisateur du même nom. L'exemple suivant illustre la manière d'appeler la fonction système intégrée fn_helpcollations.

SELECT *
FROM sys.fn_helpcollations();
GO

Utilisation d'indicateurs pour les fonctions table

Lorsque vous créez une fonction définie par l'utilisateur, vous pouvez appliquer un indicateur de table pour toute requête constituant la définition de la fonction. Les indicateurs appliqués aux vues qui font référence à des fonctions table dans Transact-SQL sont également appliqués à ces fonctions. Il peut arriver qu'une fonction soit en conflit avec les indicateurs spécifiés dans la définition de la fonction. Pour plus d'informations, consultez Résolution de vues.

Vous ne pouvez pas appliquer des indicateurs aux vues qui font référence à des fonctions table CLR.

Remarque :
La capacité du moteur de base de données à appliquer des indicateurs aux vues faisant référence à des fonctions table à plusieurs instructions qui font partie de la définition de ces vues sera supprimée dans une version ultérieure de SQL Server.

Vous ne pouvez pas appliquer un indicateur de table aux résultats des fonctions table dans la clause FROM d'une requête.

Voir aussi

Concepts

Création de fonctions définies par l'utilisateur (moteur de base de données)
Création de fonctions CLR
Création d'agrégats définis par l'utilisateur
Affichage des fonctions définies par l'utilisateur

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications