CREATE FUNCTION (Transact-SQL)

Mis à jour : 14 avril 2006

Crée une fonction définie par l'utilisateur. Celle-ci est une routine CLR (Common Language Runtime) ou Transact-SQL enregistrée qui retourne une valeur. Les fonctions définies par l'utilisateur ne permettent pas d'exécuter des actions qui modifient l'état des bases de données. À l'image des fonctions système, elles peuvent être appelées à partir d'une requête. Les fonctions scalaires peuvent être exécutées à l'aide d'une instruction EXECUTE, comme les procédures stockées.

ALTER FUNCTION et DROP FUNCTION permettent respectivement de modifier et supprimer les fonctions définies par l'utilisateur.

Conventions de la syntaxe de Transact-SQL

Syntaxe

Scalar Functions
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
                function_body 
        RETURN scalar_expression
    END
[ ; ]

Inline Table-valued Functions
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Multistatement Table-valued Functions
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
  ]
)
RETURNS @return_variable TABLE < table_type_definition >
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
                function_body 
        RETURN
    END
[ ; ]

CLR Functions
CREATE FUNCTION [ schema_name. ] function_name 
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type 
        [ = default ] } 
    [ ,...n ]
)
RETURNS { return_data_type | TABLE <clr_table_type_definition> }
    [ WITH <clr_function_option> [ ,...n ] ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

Method Specifier
<method_specifier>::=
    assembly_name.class_name.method_name

Function Options
<function_option>::= 
{
    [ ENCRYPTION ]
  | [ SCHEMABINDING ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

<clr_function_option>::=
}
    [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

Table Type Definitions
<table_type_definition>:: = 
( { <column_definition> <column_constraint> 
  | <computed_column_definition> } 
        [ <table_constraint> ] [ ,...n ]
) 

<clr_table_type_definition>::= 
( { column_name data_type } [ ,...n ] )

<column_definition>::=
{
    { column_name data_type }
    [ [ DEFAULT constant_expression ] 
      [ COLLATE collation_name ] | [ ROWGUIDCOL ]
    ]
    | [ IDENTITY [ (seed , increment ) ] ]
    [ <column_constraint> [ ...n ] ] 
}
<column_constraint>::= 
{
    [ NULL | NOT NULL ] 
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ] 
        [ WITH FILLFACTOR = fillfactor 
        | WITH ( < index_option > [ , ...n ] )
      [ ON { filegroup | "default" } ]
  | [ CHECK ( logical_expression ) ] [ ,...n ]
}

<computed_column_definition>::=
column_name AS computed_column_expression 

<table_constraint>::=
{ 
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ] 
            ( column_name [ ASC | DESC ] [ ,...n ] )
        [ WITH FILLFACTOR = fillfactor 
        | WITH ( <index_option> [ , ...n ] )
  | [ CHECK ( logical_expression ) ] [ ,...n ]
}

<index_option>::=
{ 
    PAD_INDEX = { ON | OFF } 
  | FILLFACTOR = fillfactor 
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS ={ ON | OFF } 
}

Arguments

• schema_name
  Nom du schéma auquel appartient la fonction définie par l'utilisateur.

• function_name
  Nom de la fonction définie par l'utilisateur. Les noms de fonctions doivent respecter les règles applicables aux identificateurs et doivent être uniques dans la base de données et pour son schéma.

  Remarque :
  Les parenthèses sont requises après le nom de fonction même si aucun paramètre n'est spécifié.

• **@**parameter_name
  Paramètre dans la fonction définie par l'utilisateur. Un ou plusieurs paramètres peuvent être déclarés.

  Une fonction peut comprendre au maximum 1 024 paramètres. La valeur de chaque paramètre déclaré doit être fournie par l'utilisateur lors de l'exécution de la fonction, sauf si vous définissez une valeur par défaut pour le paramètre.

  Spécifiez un nom de paramètre en plaçant le signe @ comme premier caractère. Le nom de paramètre doit suivre les règles applicables aux identificateurs. Un paramètre étant local à une fonction, vous pouvez utiliser le même nom dans d'autres fonctions. Les paramètres ne peuvent que prendre la place de constantes ; ils ne peuvent pas être utilisés à la place de noms de tables, de colonnes ou d'autres objets de base de données.

  Remarque :

  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 de lot. Par exemple, si vous définissez une variable en tant que char(3), puis que vous lui attribuez une valeur comprenant plus de trois caractères, les données sont tronquées au niveau de la taille définie et l'instruction INSERT ou UPDATE réussit.

• [ type_schema_name**.** ] parameter_data_type
  Type de données de paramètre et, éventuellement, schéma auquel il appartient. Dans le cas des fonctions Transact-SQL, tous les types de données, y compris les types CLR définis par l'utilisateur, sont autorisés, à l'exception du type de données timestamp. Dans le cas des fonctions CLR, tous les types de données, y compris les types CLR définis par l'utilisateur, sont autorisés, à l'exception des types de données text, ntext, image et timestamp. Les types non scalaires cursor et table ne peuvent pas être spécifiés en tant que type de données de paramètre dans les fonctions Transact-SQL ou CLR.

  Si l'argument type_schema_name n'est pas précisé, le moteur de base de données SQL Server 2005 cherche la valeur de parameter_data_type dans l'ordre suivant :

  • Le schéma qui contient les noms des types de données système SQL Server.
  • Le schéma par défaut de l'utilisateur actuel dans la base de données active.
  • Le schéma dbo dans la base de données active.

• [ **=**default ]
  Valeur par défaut pour le paramètre. Si vous avez défini une valeur par défaut indiquée par default, la fonction peut être exécutée sans spécifier de valeur pour ce paramètre.

  Remarque :
  Les valeurs de paramètre par défaut peuvent être spécifiées pour les fonctions CLR, à l'exception des types de données varchar(max) et varbinary(max).

  Lorsque l'un des paramètres de la fonction possède une valeur par défaut, le mot clé DEFAULT doit être spécifié lors de l'appel de la fonction afin de récupérer la valeur par défaut. Ce comportement est différent de l'utilisation de paramètres avec des valeurs par défaut dans des procédures stockées pour lesquelles l'omission du paramètre implique également la prise en compte de la valeur par défaut.

• return_data_type
  Valeur de retour d'une fonction scalaire définie par l'utilisateur. Dans le cas des fonctions Transact-SQL, tous les types de données, y compris les types CLR définis par l'utilisateur, sont autorisés, à l'exception du type de données timestamp. Dans le cas des fonctions CLR, tous les types de données, y compris les types CLR définis par l'utilisateur, sont autorisés, à l'exception des types de données text, ntext, image et timestamp. Les types non scalaires cursor et table ne peuvent pas être spécifiés en tant que type de données de retour dans les fonctions Transact-SQL ou CLR.

• function_body
  Spécifie qu'une série d'instructions Transact-SQL qui ne produisent pas ensemble un effet secondaire, tel que la modification d'une table, définissent la valeur de la fonction. function_body est uniquement utilisé dans les fonctions scalaires et les fonctions à instructions multiples comportant des valeurs de table.

  Dans les fonctions scalaires, function_body représente une série d'instructions Transact-SQL qui, ensemble, retournent une valeur scalaire.

  Dans les fonctions à instructions multiples comportant des valeurs de table, function_body est une série d'instructions Transact-SQL qui remplissent une variable de retour TABLE.

• scalar_expression
  Indique la valeur scalaire retournée par la fonction scalaire.

• TABLE
  Indique que la valeur retournée de la fonction table est une table. Seules les constantes et les **@**local_variables peuvent être transmises aux fonctions table.

  Dans les fonctions table en ligne, la valeur retournée TABLE est définie par le biais d'une instruction SELECT unique. Aucune variable retournée n'est associée à une fonction en ligne.

  Dans les fonctions table à instructions multiples, **@**return_variable est une variable TABLE, utilisée pour stocker et accumuler les lignes à retourner en guise de valeur de la fonction. **@**return_variable peut être spécifié uniquement pour les fonctions Transact-SQL, pas pour les fonctions CLR.

• select_stmt
  Représente l'instruction SELECT unique qui définit la valeur retournée d'une fonction table en ligne.

• EXTERNAL NAME <method_specifier>, assembly_name.class_name.method_name
  Spécifie la méthode d'un assembly à lier à la fonction. assembly_name doit correspondre à un assembly existant visible sous SQL Server dans la base de données active. class_name doit être un identificateur SQL Server valide et faire office de classe dans l'assembly. Si la classe porte un nom qualifié par un espace de noms dont les parties sont séparées par un point (.), ce nom doit être délimité à l'aide de crochets ([]) ou de guillemets (""). method_name doit être un identificateur SQL Server valide et faire office de méthode statique dans la classe spécifiée.

  Remarque :
  Par défaut, SQL Server ne peut pas exécuter le code CLR. Vous pouvez créer, modifier et supprimer des objets de base de données qui référencent des modules CLR (Common Language Runtime) ; toutefois, vous ne pouvez pas exécuter ces références dans SQL Server tant que vous n'avez pas activé l'option clr enabled option. Pour activer cette option, utilisez sp_configure.

• <table_type_definition>, ( { <column_definition> <column_constraint> ,   | <computed_column_definition> } ,   [ <table_constraint> ] [ ,...n ], ) ,
  Définit le type de données de table pour une fonction Transact-SQL. La déclaration de table comprend des définitions de colonne et des contraintes de colonne ou de table. La table est toujours placée dans le groupe de fichiers primaire.

• < clr_table_type_definition > , ( { column_namedata_type } [ ,...n ] ),
  Définit les types de données de table pour une fonction CLR. La déclaration de table ne comprend que des types de données et des noms de colonne. La table est toujours placée dans le groupe de fichiers primaire.

<function_option>::= and <clr_function_option>::=

Spécifie que la fonction aura une ou plusieurs des options suivantes.

• ENCRYPTION
  Indique que le moteur de base de données se charge de convertir le texte d'origine provenant de l'instruction CREATE FUNCTION dans un format obscurci. La sortie générée par l'obfuscation n'est pas visible directement dans les affichages catalogue de SQL Server 2005. Les utilisateurs n'ayant pas accès aux tables système ou aux fichiers de base de données ne peuvent pas récupérer le texte d'obfuscation. Le texte est cependant à la disposition des utilisateurs dotés de privilèges qui accèdent aux tables système via le port DAC ou qui accèdent directement aux fichiers de bases de données. Les utilisateurs qui peuvent associer un débogueur au processus serveur peuvent également récupérer la procédure d'origine de la mémoire au moment de l'exécution. Pour plus d'informations sur l'accès aux métadonnées système, consultez Configuration de la visibilité des métadonnées.

  L'utilisation cette option empêche la publication de la fonction dans le cadre de la réplication SQL Server. Cette option ne peut pas être spécifiée pour les fonctions CLR.

• SCHEMABINDING
  Indique que la fonction est liée aux objets de base de données auxquels elle fait référence. Cette condition empêche que des modifications ne soient apportées à la fonction si d'autres objets liés au schéma y font référence.

  La liaison de la fonction aux objets auxquels elle fait référence est supprimée uniquement lorsqu'une des actions suivantes se produit :

  • La fonction est supprimée.
  • La fonction est modifiée, avec l'instruction ALTER, sans spécification de l'option SCHEMABINDING.

  Une fonction peut être liée au schéma uniquement si les conditions suivantes sont vérifiées :

  • La fonction est une fonction Transact-SQL.
  • Les fonctions utilisateur et vues référencées par la fonction sont également liées au schéma.
  • La fonction fait référence aux objets à partir d'un nom en deux parties.
  • La fonction et les objets auxquels elle fait référence appartiennent à la même base de données.
  • L'utilisateur qui exécute l'instruction CREATE FUNCTION dispose de l'autorisation REFERENCES pour les objets de base de données auxquels la fonction fait référence.

  L'argument SCHEMABINDING ne peut pas être spécifié pour les fonctions CLR ou celles qui référencent des types de données d'alias.

• RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT
  Spécifie l'attribut OnNULLCall d'une fonction scalaire. S'il n'est pas spécifié, l'argument CALLED ON NULL INPUT est implicite par défaut. Cela signifie que le corps de la fonction est exécuté même si NULL est transmis comme argument.

  Si l'argument RETURNS NULL ON NULL INPUT est spécifié dans une fonction CLR, il indique que SQL Server peut retourner NULL lorsque n'importe lequel des arguments qu'il reçoit a pour valeur NULL, sans réellement appeler le corps de la fonction. Si la méthode d'une fonction CLR spécifiée dans <method_specifier> possède déjà un attribut personnalisé qui indique RETURNS NULL ON NULL INPUT, mais que l'instruction CREATE FUNCTION indique CALLED ON NULL INPUT, cette instruction est prioritaire. L'attribut OnNULLCall ne peut pas être spécifié pour les fonctions table CLR.

• Clause EXECUTE AS
  Spécifie le contexte de sécurité dans lequel la fonction définie par l'utilisateur est exécutée. Par conséquent, vous pouvez choisir le compte d'utilisateur que SQL Server doit utiliser pour valider les autorisations sur tous les objets de base de données référencés par la fonction.

  Remarque :
  La clause EXECUTE AS ne peut pas être spécifiée pour les fonctions en ligne définies par l'utilisateur.

  Pour plus d'informations, consultez Clause EXECUTE AS (Transact-SQL).

< column_definition >::=

Définit le type de données de la table. La déclaration de table comprend des contraintes et des définitions de colonne. Dans le cas des fonctions CLR, seules les valeurs de column_name et de data_type peuvent être spécifiées.

• column_name
  Nom d'une colonne de la table. Les noms de colonnes doivent respecter les règles gouvernant les identificateurs et doivent être uniques dans la table. column_name peut comporter entre 1 et 128 caractères.

• data_type
  Indique le type de données de la colonne. Dans le cas des fonctions Transact-SQL, tous les types de données, y compris les types CLR définis par l'utilisateur, sont autorisés, à l'exception du type de données timestamp. Dans le cas des fonctions CLR, tous les types de données, y compris les types CLR définis par l'utilisateur, sont autorisés, à l'exception des types de données text, ntext, image, char, varchar, varchar(max) et timestamp. Le type non scalaire cursor ne peut pas être spécifié en tant que type de données de colonne dans les fonctions Transact-SQL ou CLR.

• DEFAULT constant_expression
  Spécifie la valeur fournie pour la colonne lorsque vous n'avez pas spécifié explicitement de valeur lors d'une insertion. constant_expression est une valeur de constante, NULL ou de fonction système. Les définitions DEFAULT peuvent être appliquées à n'importe quelle colonne, sauf à celles qui possèdent la propriété IDENTITY. L'argument DEFAULT ne peut pas être spécifié pour les fonctions table CLR.

• COLLATE collation_name
  Indique le classement de la colonne. Sans autre indication, le classement par défaut de la base de données est attribué à la colonne. Le nom du classement peut être un nom de classement Windows ou SQL. Pour obtenir la liste des classements ou plus d'informations à leur sujet, consultez Nom de classement Windows (Transact-SQL) et Nom du classement SQL (Transact-SQL).

  La clause COLLATE peut être utilisée pour modifier uniquement les classements des colonnes comportant les types de données char, varchar, nchar et nvarchar.

  L'argument COLLATE ne peut pas être spécifié pour les fonctions table CLR.

• ROWGUIDCOL
  Indique que la nouvelle colonne est une colonne d'identificateur unique global de ligne. Une seule colonne uniqueidentifier par table peut servir de colonne ROWGUIDCOL. La propriété ROWGUIDCOL ne peut être affectée qu'à une colonne uniqueidentifier.

  La propriété ROWGUIDCOL n'assure pas l'unicité des valeurs stockées dans la colonne. Elle ne peut de plus générer automatiquement de valeurs pour les nouvelles lignes insérées dans la table. Pour générer des valeurs uniques pour chaque colonne, utilisez la fonction NEWID dans des instructions INSERT. Une valeur par défaut peut être spécifiée, mais ce ne peut pas être NEWID.

• IDENTITY
  Indique que la nouvelle colonne est une colonne d'identité. Lorsqu'une ligne est ajoutée à la table, SQL Server affecte une valeur incrémentée unique à la colonne. Les colonnes d'identité sont généralement utilisées avec les contraintes PRIMARY KEY comme identificateur unique de ligne pour la table. La propriété IDENTITY peut être affectée aux colonnes tinyint, smallint, int, bigint, decimal(p,0) ou numeric(p,0). Une seule colonne d'identité peut être créée par table. Il n'est pas possible d'utiliser des valeurs par défaut liées et des contraintes DEFAULT avec une colonne d'identité. Vous devez spécifier à la fois seed et increment, ou bien aucun des deux. Si vous n'en spécifiez aucune, la valeur par défaut est (1,1).

  L'argument IDENTITY ne peut pas être spécifié pour les fonctions table CLR.

  • seed
    Valeur entière à affecter à la première ligne de la table.
  • increment
    Entier à ajouter à la valeur seed pour les lignes successives de la table.

< column_constraint >::= and < table_constraint>::=

Définit la contrainte d'une colonne ou table spécifiée. Dans le cas des fonctions CLR, le seul type de contrainte autorisé est NULL. L'utilisation de contraintes nommées n'est pas autorisée.

• NULL et NOT NULL
  Détermine si les valeurs NULL sont autorisées dans la colonne. NULL n'est pas strictement une contrainte, mais peut être spécifié comme NOT NULL. L'argument NOT NULL ne peut pas être spécifié pour les fonctions table CLR.

• PRIMARY KEY
  Contrainte assurant l'intégrité de l'entité d'une colonne spécifiée au moyen d'un seul index. Dans les fonctions table définies par l'utilisateur, la contrainte PRIMARY KEY ne peut être créée que sur une colonne par table. L'argument PRIMARY KEY ne peut pas être spécifié pour les fonctions table CLR.

• UNIQUE
  Contrainte assurant l'intégrité de l'entité d'une colonne ou de plusieurs colonnes spécifiées au moyen d'un seul index. Une table peut comprendre plusieurs contraintes UNIQUE. L'argument UNIQUE ne peut pas être spécifié pour les fonctions table CLR.

• CLUSTERED et NONCLUSTERED
  Indique qu'un index, cluster ou non cluster, est créé pour la contrainte PRIMARY KEY ou UNIQUE. Les contraintes PRIMARY KEY utilisent CLUSTERED, tandis que les contraintes UNIQUE recourent à NONCLUSTERED.

  CLUSTERED peut être spécifié pour une seule contrainte. Si CLUSTERED est spécifié pour une contrainte UNIQUE et qu'une contrainte PRIMARY KEY est également spécifiée, la contrainte PRIMARY KEY utilise NONCLUSTERED.

  Les arguments CLUSTERED et NONCLUSTERED ne peuvent pas être spécifiés pour les fonctions table CLR.

• CHECK
  Contrainte qui assure l'intégrité du domaine en limitant les valeurs possibles pouvant être entrées dans une ou plusieurs colonnes. Les contraintes CHECK ne peuvent pas être spécifiées pour les fonctions table CLR.

  • logical_expression
    Expression logique qui retourne TRUE ou FALSE.

<computed_column_definition>::=

Spécifie une colonne calculée. Pour plus d'informations sur les colonnes calculées, consultez CREATE TABLE (Transact-SQL).

• column_name
  Nom de la colonne calculée.

• computed_column_expression
  Expression définissant la valeur d'une colonne calculée.

<index_option>::=

Spécifie les options de l'index PRIMARY KEY ou UNIQUE. Pour plus d'informations sur les options d'index, consultez CREATE INDEX (Transact-SQL).

• PAD_INDEX = { ON | OFF }
  Spécifie le remplissage de l'index. La valeur par défaut est OFF.

• FILLFACTOR = fillfactor
  Pourcentage indiquant l'espace occupé par le niveau de feuille de chaque page d'index opéré par le moteur de base de données lors de la création ou la modification d'index. fillfactor doit être de type entier et compris entre 1 et 100. La valeur par défaut est 0.

• IGNORE_DUP_KEY = { ON | OFF }
  Spécifie la réponse d'erreur aux valeurs de clé dupliquées dans une transaction d'insertion de plusieurs lignes sur un index unique cluster ou non cluster. La valeur par défaut est OFF.

• STATISTICS_NORECOMPUTE = { ON | OFF }
  Indique si les statistiques de distribution sont recalculées. La valeur par défaut est OFF.

• ALLOW_ROW_LOCKS = { ON | OFF }
  Indique si les verrous de ligne sont autorisés. La valeur par défaut est ON.

• ALLOW_PAGE_LOCKS = { ON | OFF }
  Indique si les verrous de page sont autorisés. La valeur par défaut est ON.

Notes

Les fonctions définies par l'utilisateur sont scalaires ou de type table. Les fonctions sont scalaires si la clause RETURNS spécifie l'un des types de données scalaires. Les fonctions scalaires peuvent être définies à l'aide de plusieurs instructions Transact-SQL.

Les fonctions sont de type table si la clause RETURNS spécifie TABLE. La définition du corps d'une fonction table détermine si celle-ci est une fonction en ligne ou multi-instructions. Pour plus d'informations, consultez Fonctions table définies par l'utilisateur.

Les instructions suivantes sont valides dans une fonction :

• Instructions d'affectation
• Instructions de contrôle de flux à l'exception des instructions TRY...CATCH
• Instructions DECLARE définissant des variables de données locales et des curseurs locaux
• Instructions SELECT qui contiennent des listes de sélection avec des expressions affectant des valeurs aux variables locales
• Opérations de curseur faisant référence à des curseurs locaux déclarés, ouverts, fermés et désalloués dans la fonction. Seules les instructions FETCH affectant des valeurs aux variables locales à l'aide de la clause INTO sont autorisées ; les instructions FETCH qui retournent des données au client ne sont pas autorisées.
• Instructions INSERT, UPDATE et DELETE modifiant des variables table locales.
• Instructions EXECUTE appelant des procédures stockées étendues
• Pour plus d'informations, consultez Création de fonctions définies par l'utilisateur (moteur de base de données).

Imbrication des fonctions définies par l'utilisateur

Les fonctions définies par l'utilisateur peuvent être imbriquées ; en d'autres termes, une fonction définie par l'utilisateur peut en 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.

Remarque :
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 depuis du code managé n'entrent pas en compte dans cette limite.

Propriétés des fonctions

Dans les versions antérieures de SQL Server, les fonctions sont uniquement classées en fonctions déterministes ou non déterministes. Dans SQL Server 2005, les fonctions possèdent les propriétés suivantes. Les valeurs de ces propriétés déterminent si les fonctions sont utilisables dans des colonnes calculées pouvant être indexées ou rendues persistantes.

Propriété	Description	Remarques
IsDeterministic	La fonction est déterministe ou non déterministe.	L'accès local aux données est autorisé dans les fonctions déterministes. Par exemple, une fonction est dite déterministe si elle retourne toujours le même résultat chaque fois qu'elle est appelée à l'aide d'un ensemble spécifique de valeurs d'entrée et que la base de données présente le même état.
IsPrecise	La fonction est précise ou imprécise.	Les fonctions imprécises contiennent des opérations telles que les opérations à virgule flottante.
IsSystemVerified	Les propriétés de précision et de déterminisme de la fonction peuvent être vérifiées par SQL Server.
SystemDataAccess	La fonction accède aux données système (catalogues système ou tables système virtuelles) dans l'instance locale de SQL Server.
UserDataAccess	La fonction accède aux données utilisateur dans l'instance locale de SQL Server.	Comprend les tables définies par l'utilisateur et les tables temporaires, mais pas les variables de table.

Les propriétés de précision et de déterminisme des fonctions Transact-SQL sont automatiquement déterminées par SQL Server. Pour plus d'informations, consultez Recommandations pour la conception des fonctions définies par l'utilisateur. Les propriétés d'accès aux données et de déterminisme des fonctions CLR peuvent être spécifiées par l'utilisateur. Pour plus d'informations, consultez Overview of CLR Integration Custom Attributes.

Pour afficher les valeurs actuelles de ces propriétés, utilisez OBJECTPROPERTYEX.

Indexation de colonnes calculées qui appellent une fonction définie par l'utilisateur

Une colonne calculée qui appelle une fonction définie par l'utilisateur peut être utilisée dans un index lorsque la fonction possède les valeurs de propriété suivantes :

• IsDeterministic = true
• IsSystemVerified = true (sauf si la colonne calculée est persistante)
• UserDataAccess = false
• SystemDataAccess = false

Pour plus d'informations, consultez Création d'index sur des colonnes calculées.

Appel de procédures stockées étendues à partir de fonctions

Une procédure stockée étendue appelée à partir d'une fonction ne peut pas retourner de jeux de résultats au client. Les API ODS qui retournent des jeux de résultats au client retournent FAIL. La procédure stockée étendue peut se reconnecter à une instance de SQL Server ; toutefois, elle ne doit pas rejoindre la même transaction que la fonction l'ayant appelée.

À l'image des appels à partir d'un lot ou d'une procédure stockée, la procédure stockée étendue est exécutée dans le contexte du compte de sécurité Windows sous lequel SQL Server est exécuté. Le propriétaire de la procédure stockée doit tenir compte de cet élément lors de l'octroi de l'autorisation EXECUTE sur la procédure.

Appel des fonctions

Les fonctions scalaires peuvent être appelées là où sont utilisées les expressions scalaires. C'est notamment le cas dans les colonnes calculées et les définitions de contraintes CHECK. Les fonctions scalaires peuvent également être exécutées à l'aide de l'instruction EXECUTE. Les fonctions scalaires doivent être appelées à l'aide, au moins, du 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). Les fonctions table peuvent être appelées là où sont autorisées les expressions de table dans la clause FROM des instructions SELECT, INSERT, UPDATE ou DELETE. Pour plus d'informations, consultez Exécution de fonctions définies par l'utilisateur (Moteur de base de données).

Utilisation de paramètres et de valeurs de retour dans les fonctions CLR

Si des paramètres sont spécifiés dans une fonction CLR, ils doivent être de types SQL Server tels que préalablement définis pour scalar_parameter_data_type. Pour une comparaison des types de données système SQL Server aux types de données d'intégration CLR ou aux types de données CLR .NET Framework, consultez SQL Server Data Types and Their .NET Framework Equivalents.

Pour que SQL Server référence la méthode adéquate surchargée dans une classe, la méthode indiquée dans <method_specifier> doit présenter les caractéristiques suivantes :

• Recevoir le même nombre de paramètres que ceux spécifiés dans [ ,...n ].
• Recevoir tous les paramètres par valeur, non par référence.
• Utiliser des types de paramètre compatibles avec ceux spécifiés dans la fonction SQL Server.

Si le type de données de retour de la fonction CLR spécifie un type de table (RETURNS TABLE), le type de données de retour de la méthode dans <method_specifier> doit être de type IEnumerator ou IEnumerable et le système considère que l'interface est implémentée par le créateur de la fonction. À la différence des fonctions Transact-SQL, les fonctions CLR ne peuvent pas comprendre de contraintes PRIMARY KEY, UNIQUE ou CHECK dans <table_type_definition>. Les types de données des colonnes spécifiées dans <table_type_definition> doivent correspondre aux types des colonnes correspondantes du jeu de résultats retourné par la méthode dans <method_specifier> au moment de l'exécution. Cette vérification de type n'est pas réalisée à la création de la fonction.

Pour plus d'informations sur la programmation des fonctions CLR, consultez CLR User-Defined Functions.

Instructions SQL interdites

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

Visualisation d'informations sur les fonctions

Pour afficher la définition d'une fonction Transact-SQL définie par l'utilisateur, utilisez l'affichage catalogue sys.sql_modules de la base de données qui héberge la fonction.

Exemple :

USE AdventureWorks;
GO
SELECT definition, type 
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON m.object_id = o.object_id 
    AND type IN ('FN', 'IF', 'TF');
GO

Remarque :
La définition de fonctions créées à l'aide de l'option ENCRYPTION ne peut pas être visualisée à l'aide de sys.sql_modules ; toutefois, d'autres informations sur les fonctions chiffrées apparaissent.

Pour afficher les informations relatives à une fonction CLR définie par l'utilisateur, utilisez l'affichage catalogue sys.assembly_modules de la base de données qui héberge la fonction.

Pour afficher les informations relatives aux paramètres configurés dans une fonction définie par l'utilisateur, utilisez l'affichage catalogue sys.parameters de la base de données qui héberge la fonction.

Pour obtenir une liste des objets référencés par une fonction, utilisez sys.sql_dependencies.

Autorisations

Requiert l'autorisation CREATE FUNCTION dans la base de données et l'autorisation ALTER sur le schéma dans lequel la fonction est en cours de création. Si la fonction spécifie un type défini par l'utilisateur, elle requiert l'autorisation EXECUTE sur le type.

Exemples

A. Utilisation d'une fonction scalaire définie par l'utilisateur calculant la semaine ISO

L'exemple suivant crée la fonction définie par l'utilisateur ISOweek. Cette fonction contient un argument date et calcule le numéro de semaine ISO. Pour que ce calcul puisse être correctement réalisé, SET DATEFIRST 1 doit être appelée avant la fonction.

L'exemple illustre également l'utilisation de la clause EXECUTE AS afin d'indiquer le contexte de sécurité dans lequel une procédure stockée peut être exécutée. Dans l'exemple, l'option CALLER spécifie que la procédure sera exécutée dans le contexte de l'utilisateur qui l'appelle. Les autres options que vous pouvez spécifier sont SELF, OWNER et user_name.

Voici l'appel de la fonction. DATEFIRST a la valeur 1.

USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.ISOweek', N'FN') IS NOT NULL
    DROP FUNCTION dbo.ISOweek;
GO
CREATE FUNCTION dbo.ISOweek (@DATE datetime)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
     DECLARE @ISOweek int;
     SET @ISOweek= DATEPART(wk,@DATE)+1
          -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104');
--Special cases: Jan 1-3 may belong to the previous year
     IF (@ISOweek=0) 
          SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 
               AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1;
--Special case: Dec 29-31 may belong to the next year
     IF ((DATEPART(mm,@DATE)=12) AND 
          ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
          SET @ISOweek=1;
     RETURN(@ISOweek);
END;
GO
SET DATEFIRST 1;
SELECT dbo.ISOweek(CONVERT(DATETIME,'12/26/2004',101)) AS 'ISO Week';

Voici l'ensemble des résultats.

ISO Week
----------------
52

B. Création d'une fonction table en ligne

L'exemple suivant retourne une fonction table en ligne. Pour chaque produit vendu au magasin, il retourne trois colonnes : ProductID, Name et l'agrégation des totaux annuels par magasin sous YTD Total .

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

Pour appeler la fonction, exécutez la requête suivante :

SELECT * FROM Sales.ufn_SalesByStore (602);

C. Création d'une fonction table à instructions multiples

L'exemple suivant crée la fonction table fn_FindReports(InEmpID). Lorsqu'elle est fournie avec un ID d'employé valide, la fonction retourne une table répertoriant tous les employés qui rendent compte à l'employé directement ou indirectement. La fonction utilise une expression de table commune (CTE, Common Table Expression) récursive pour générer la liste hiérarchique des employés. Pour plus d'informations sur les expressions CTE, consultez WITH common_table_expression (Transact-SQL).

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

D. Création d'une fonction CLR

L'exemple suivant suppose que les exemples du moteur de base de données SQL Server sont installés dans l'emplacement par défaut de l'ordinateur local et que l'exemple d'application StringManipulate.csproj est compilé. Pour plus d'informations, consultez Manipulation de chaînes sensible aux caractères supplémentaires.

L'exemple crée la fonction CLR len_s. Avant que la fonction ne soit créée, l'assembly SurrogateStringFunction.dll est inscrit dans la base de données locale.

DECLARE @SamplesPath nvarchar(1024);
-- You may have to modify the value of the this variable if you have
--installed the sample someplace other than the default location.
SELECT @SamplesPath = REPLACE(physical_name, 'Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf', 'Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\') 
    FROM master.sys.database_files 
    WHERE name = 'master';

CREATE ASSEMBLY [SurrogateStringFunction]
FROM @SamplesPath + 'StringManipulate\CS\StringManipulate\bin\debug\SurrogateStringFunction.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE FUNCTION [dbo].[len_s] (@str nvarchar(4000))
RETURNS bigint
AS EXTERNAL NAME [SurrogateStringFunction].[Microsoft.Samples.SqlServer.SurrogateStringFunction].[LenS];
GO

Pour un exemple de création d'une fonction table CLR, consultez CLR Table-Valued Functions.

Voir aussi

Référence

ALTER FUNCTION (Transact-SQL)
DROP FUNCTION (Transact-SQL)
OBJECTPROPERTYEX (Transact-SQL)
sys.sql_modules (Transact-SQL)
sys.assembly_modules (Transact-SQL)
EXECUTE (Transact-SQL)
EVENTDATA (Transact-SQL)

Autres ressources

Fonctions définies par l'utilisateur (Moteur de base de données)
CLR User-Defined Functions

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications

Version	Historique
14 avril 2006	Nouveau contenu : Dans la section « Appel des fonctions », insertion de la remarque selon laquelle les fonctions scalaires doivent être appelées à l'aide, au moins, du nom en deux parties de la fonction.
5 décembre 2005	Contenu modifié : La définition de l'option ENCRYPTION a été clarifiée.