CREATE FUNCTION (Transact-SQL)

Syntaxe

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

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

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

--Transact-SQL Function Clauses 
<function_option>::= 
{
    [ ENCRYPTION ]
  | [ SCHEMABINDING ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

<table_type_definition>:: = 
( { <column_definition> <column_constraint> 
  | <computed_column_definition> } 
    [ <table_constraint> ] [ ,...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 } 
}

--CLR Scalar Function Syntax
CREATE FUNCTION [ schema_name. ] function_name 
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
)
RETURNS { return_data_type }
    [ WITH <clr_function_option> [ ,...n ] ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

--CLR Table-Valued Function Syntax
CREATE FUNCTION [ schema_name. ] function_name 
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
)
RETURNS TABLE <clr_table_type_definition> 
    [ WITH <clr_function_option> [ ,...n ] ]
    [ ORDER ( <order_clause> ) ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

--CLR Function Clauses
<order_clause> ::= 
{
   <column_name_in_clr_table_type_definition>
   [ ASC | DESC ] 
} [ ,...n] 

<method_specifier>::=
    assembly_name.class_name.method_name

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

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

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.

  Notes

  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 2 100 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.

  Notes

  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 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 et les types de tables 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 de text, ntext, image, des types de tables définis par l'utilisateur et des types de données 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 type_schema_name n'est pas spécifié, le Moteur de base de données cherche scalar_parameter_data_type dans l'ordre suivant :

  • Schéma qui contient les noms des types de données système SQL Server.

  • Schéma par défaut de l'utilisateur actuel dans la base de données active.

  • Schéma dbo dans la base de données active.

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

  Notes

  Des 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. Une exception à ce comportement concerne l'appel d'une fonction scalaire à l'aide de l'instruction EXECUTE. Avec l'instruction EXECUTE, le mot clé DEFAULT n'est pas obligatoire.

• READONLY
  Indique que le paramètre ne peut pas être mis à jour ni modifié dans la définition de la fonction. Si le type de paramètre est un type de table défini par l'utilisateur, READONLY doit être spécifié.

• 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 et 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 transmis aux fonctions table.

  Dans les fonctions table en ligne, la valeur de retour 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.

  Attention

  L'intégration à une fonction table à instructions multiples dans une clause FROM est possible, mais peut offrir des performances médiocres. SQL Server n'est pas en mesure d'utiliser toutes les techniques optimisées avec certaines instructions qui peuvent être incluses dans une fonction à instructions multiples, ce qui peut générer un plan de requête non optimal. Pour obtenir les meilleures performances possibles, utilisez des jointures entre les tables de base au lieu de fonctions chaque fois que cela est possible.

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

• ORDER (<order_clause>)
  Spécifie l'ordre dans lequel les résultats sont retournés à partir de la fonction table. Pour plus d'informations, consultez la section « Instructions d'utilisation de l'ordre de tri », plus loin dans cette rubrique.

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

  Notes

  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 activé. 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.

Meilleures pratiques

Si une fonction définie par l'utilisateur 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 lors de la création de la fonction. Vous vous assurez ainsi que 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.

Types de données

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 obtenir des informations sur la comparaison des types de données système SQL Server avec les types de données d'intégration CLR ou les types de données CLR .NET Framework, consultez Mappage des données de paramètres CLR.

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 façon de programmer des fonctions CLR, consultez Fonctions CLR définies par l'utilisateur.

Remarques d'ordre général

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

Interopérabilité

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 libéré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 de 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).

Interopérabilité des colonnes calculées

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

Limitations et restrictions

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 contenir de clause OUTPUT INTO avec une table comme cible.

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

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

Utilisation de l'ordre de tri dans les fonctions table CLR

Métadonnées

Le tableau suivant répertorie les affichages catalogue système que vous pouvez utiliser pour retourner des métadonnées sur les fonctions définies par l'utilisateur.

USE AdventureWorks2008R2;
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

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

USE AdventureWorks2008R2;
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';

B. Création d'une fonction table incluse

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

SELECT * FROM Sales.ufn_SalesByStore (602);

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

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

D. Création d'une fonction CLR

DECLARE @SamplesPath nvarchar(1024);
-- You may have to modify the value of this variable if you have
-- installed the sample in a location other than the default location.
SELECT @SamplesPath = REPLACE(physical_name, 'Microsoft SQL Server\MSSQL10_5.MSSQLSERVER\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

Voir aussi

Référence

Autres ressources

Historique des modifications