CREATE FUNCTION

S’applique à :Point de terminaison d’analytique SQL dans Microsoft Fabric et Warehouse dans Microsoft Fabric

CREATE FUNCTION crée des fonctions à valeurs de table en ligne et des fonctions scalaires.

Une fonction définie par l’utilisateur est une routine Transact-SQL qui accepte des paramètres, effectue une action telle qu’un calcul complexe, et renvoie le résultat de cette action sous forme de valeur. Les fonctions scalaires retournent une valeur scalaire, telle qu’un nombre ou une chaîne. Les fonctions table définies par l’utilisateur retournent une table.

Utilisez CREATE FUNCTION pour créer une routine T-SQL réutilisable que vous pouvez utiliser de la manière suivante :

• Dans les instructions Transact-SQL telles que SELECT
• Dans Transact-SQL instructions de manipulation de données (DML) telles que UPDATE, INSERTet DELETE
• dans des applications appelant la fonction ;
• dans la définition d'une autre fonction définie par l'utilisateur ;
• pour remplacer une procédure stockée.

Vous pouvez spécifier CREATE OR ALTER FUNCTION la création d’une nouvelle fonction si elle n’existe pas sous ce nom, ou modifier une fonction existante, dans une seule phrase.

Conventions de la syntaxe Transact-SQL

Syntaxe

Syntaxe de la fonction scalaire

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

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

Syntaxe de la fonction table inline

CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] parameter_data_type
    [ = default ] }
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH SCHEMABINDING ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Les 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 suivre les règles des identifiants et être uniques au sein de la base de données et de son schéma.

@ parameter_name

Paramètre de la fonction définie par l’utilisateur. Vous pouvez déclarer un ou plusieurs paramètres.

Une fonction peut avoir jusqu’à 2 100 paramètres. Lorsqu’un utilisateur ou une application appelle une fonction, la valeur de chaque paramètre déclaré doit être fournie, sauf si un paramètre par défaut est défini.

Spécifiez un nom de paramètre en utilisant un signe @ comme premier caractère. Le nom du paramètre doit suivre les règles des identifiants. Les paramètres sont locaux à la fonction ; Vous pouvez utiliser les mêmes noms de paramètres dans d’autres fonctions. Les paramètres ne peuvent remplacer que les constantes ; ils ne peuvent pas être utilisés à la place des noms de tables, des noms de colonnes ou d’autres objets de base de données.

parameter_data_type

Type de données de paramètre. Pour les fonctions Transact-SQL, tous les types de données scalaires pris en charge sont autorisés.

[ = par défaut ]

Valeur par défaut du paramètre. Si vous définissez une valeur par défaut , vous pouvez exécuter la fonction sans spécifier une valeur pour ce paramètre.

Lorsqu’un paramètre de la fonction a une valeur par défaut, vous devez spécifier le mot-clé DEFAULT lors de l’appel de la fonction pour 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.

Pour les fonctions dans Fabric Data Warehouse, tous les types de données sont autorisés à l’exception del’horodatage/. Les types non scalaires comme les tables ne sont pas autorisés.

function_body

Série d’instructions Transact-SQL.

Dans les fonctions scalaires, function_body est une série d’instructions Transact-SQL qui évaluent ensemble une valeur scalaire, qui peut inclure :

• Expression d’instruction unique
• Expressions multi-instructions (IF/THEN/ELSE et BEGIN/END blocs)
• Variables locales
• Appels aux fonctions SQL intégrées disponibles
• Appels à d’autres fonctions définies par l’utilisateur
• SELECT instructions et références aux tables, vues et fonctions table inline
• Instructions de contrôle du flux (WHILE boucles, RETURNS)

scalar_expression

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

select_stmt

Instruction unique SELECT qui définit la valeur de retour d’une fonction table inline. Pour une fonction à valeurs de table en ligne, il n’existe pas de corps de fonction ; Le tableau est l’ensemble des résultats d’une seule SELECT affirmation.

TABLE

Indique que la valeur de retour de la fonction table est une table. Vous ne pouvez passer que des constantes et @local_variables aux TVFs.

Dans les TVF en ligne (aperçu), vous définissez la TABLE valeur de retour à travers une seule SELECT instruction. Les fonctions en ligne n’ont pas de variables de retour associées.

<function_option>

Dans Fabric Data Warehouse, les INLINEmots-clés , ENCRYPTION, et EXECUTE AS ne sont pas pris en charge.

Les options de fonctions prises en charge incluent :

SCHEMABINDING

Indique que la fonction est liée aux objets de base de données auxquels elle fait référence. Lorsque vous spécifiez SCHEMABINDING, vous ne pouvez pas modifier les objets sous-jacents (comme une vue ou une table, par exemple) d’une manière qui affecte la définition de la fonction. Vous devez d’abord modifier ou supprimer la définition de la fonction pour supprimer les dépendances sur l’objet que vous souhaitez modifier.

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

• Tu laisses tomber la fonction.

• Vous ALTER utilisez la fonction et supprimez l’option SCHEMABINDING .

Vous ne pouvez lier un schéma à une fonction que si les conditions suivantes sont vraies :

• Toutes les fonctions définies par l’utilisateur auxquelles la fonction fait référence sont également liées au schéma.

• La fonction fait référence aux objets en utilisant un nom en deux parties.

• Dans l’ensemble des UDF, vous ne pouvez référencer que les fonctions intégrées et d’autres UDF dans la même base de données.

• L’utilisateur qui exécute l’instruction CREATE FUNCTION dispose de la permission REFERENCES sur les objets de base de données auxquels la fonction fait référence.

Pour supprimer SCHEMABINDING, utilisez ALTER.

RENVOIE NULL SUR L’ENTRÉE NULL | APPELÉ SUR L’ENTRÉE NULL

Spécifie l’attribut OnNULLCall d’une fonction scalaire. Si vous ne spécifiez pas cet attribut, CALLED ON NULL INPUT il est implicite par défaut, et le corps de la fonction s’exécute même si NULL il est passé comme un argument.

Bonnes pratiques

• Si vous ne créez pas de fonction définie par l’utilisateur avec la liaison de schéma, les modifications des objets sous-jacents peuvent affecter la définition de la fonction et provoquer des résultats inattendus lors de l’invocation. Lorsque vous spécifiez WITH SCHEMABINDING la création de la fonction, vous vous assurez que les modifications ultérieures des objets sous-jacents ne peuvent pas modifier ni casser le comportement de la fonction.

• Écrivez vos fonctions définies par l’utilisateur pour qu’elles soient inligneables. Pour plus d’informations, consultez Incorporation des fonctions UDF scalaires.

Interopérabilité

Fonctions définies par l’utilisateur inline

Une fonction à valeurs de table en ligne n’accepte qu’une seule SELECT affirmation.

Fonctions définies par l’utilisateur scalaires

• Les instructions suivantes sont valides dans une fonction scalaire :

  • Instructions d'affectation
  • Instructions Control-of-Flow, à l’exception TRY...CATCH des instructions
  • DECLARE instructions définissant des variables de données locales

• Les fonctions intégrées suivantes ne sont pas prises en charge dans un corps de fonction scalaire :

  • NEWID()
  • RAND()
  • Fonctions de configuration
  • DATABASEPROPERTYEX
  • OBJECTPROPERTYEX
  • SERVERPROPERTY
  • NEXT VALUE FOR
  • COLLATIONPROPERTY
  • HAS_PERMS_BY_NAME
  • HAS_DBACCESS

• Les fonctions définies par l’utilisateur scalaires ne peuvent pas être utilisées dans une requête sur une SELECT ... FROM table utilisateur quand :

  • Le corps UDF contient un appel à une fonction intégrée non déterministe (telle que GETDATE()), voir Fonctions déterministes et non déterministes.
  • L’organisme de l’UDF contient BREAK une déclaration CONTINUE .
  • Il existe un appel scalaire récursif à UDF.

• Un UDF scalaire ne peut pas être utilisé dans toutes les formes de requête, telles que les CTE et GROUP BY, si :

  • L’UDF scalaire contient l’un de ces types de données en tant que paramètre d’entrée, variable locale ou type de données de retour : varchar(max),nvarchar(max), varbinary(max), binary(max).
  • Le corps scalaire des UDF contient des appels à d’autres UDF scalaires.
  • Le corps scalaire UDF contient des références aux tableaux/vues/iTVF.

Pour plus d’informations, consultez la configuration requise pour l’incorporation des fonctions UDF Scalar.

• Si une UDF scalaire contient l’un des éléments suivants, une requête utilisateur peut échouer si plus de 10 appels UDF sont effectués dans une seule requête. Dans certains cas de périphérie, la complexité de la requête utilisateur et du corps UDF empêche l’incorporation, auquel cas la fonction UDF scalaire n’est pas inline et la requête utilisateur échoue.

  • L’UDF scalaire contient l’un de ces types de données en tant que paramètre d’entrée, variable locale ou type de données de retour : varchar(max),nvarchar(max), varbinary(max), binary(max).
  • Le corps scalaire des UDF contient des appels à d’autres UDF scalaires.
  • Le corps scalaire UDF contient des références aux tableaux/vues/iTVF.

• Lorsqu’une fonction UDF scalaire est utilisée dans n’importe quel scénario non pris en charge, un message d’erreur s’affiche «Scalar UDF execution is currently unavailable in this context. »

Limites

Vous ne pouvez pas utiliser des fonctions définies par l’utilisateur pour effectuer des actions qui modifient l’état de la base de données.

Vous pouvez imbriquer des fonctions définies par l’utilisateur. Autrement dit, une fonction définie par l’utilisateur peut appeler une autre. Le niveau de nesting augmente lorsque la fonction appelée commence son exécution, et diminue lorsque la fonction appelée termine son exécution. Dans Fabric Data Warehouse, vous pouvez imbriquer des fonctions définies par l’utilisateur jusqu’à quatre niveaux lorsqu’un corps UDF référence une table, une vue ou une fonction à valeurs de table en ligne, ou jusqu’à 32 niveaux autrement. Si vous dépassez les niveaux maximaux de nesting, la chaîne de fonctions appelante échoue.

Métadonnées

Cette section répertorie les vues de catalogue système que vous pouvez utiliser pour retourner des métadonnées sur les fonctions définies par l’utilisateur.

• sys.sql_modules : Affiche la définition des fonctions définies par Transact-SQL utilisateur, ainsi que les informations sur l’inligneabilité. Par exemple :

   SELECT 
       SCHEMA_NAME(o.schema_id) AS SchemaName,
       o.name AS FunctionName,
       m.definition AS FunctionDefinition,
       m.is_inlineable AS Inlineable,
       m.inline_eligibility_mask AS InlineEligibilityMask
   FROM sys.objects o
   JOIN sys.sql_modules m ON o.object_id = m.object_id
   WHERE o.type = 'FN';
  

• sys.parameters : affiche des informations sur les paramètres définis dans les fonctions définies par l’utilisateur.

• sys.sql_expression_dependencies : affiche les objets sous-jacents référencés par une fonction.

Autorisations

Les membres des rôles Administrateur, Membre et Contributeur de l’espace de travail Fabric peuvent créer des fonctions.

Incorporation (inlining) des fonctions UDF scalaires

Microsoft Fabric Data Warehouse utilise différentes techniques d’inlining pour compiler et exécuter du code défini par l’utilisateur de manière distribuée.

L’inlining de l’UDF scalaire est activé par défaut.

Certaines syntaxes T-SQL rendent non linéaire une fonction UDF scalaire. Par exemple, les fonctions qui contiennent une combinaison d’une WHILE boucle et qui font référence à une table à l’intérieur du corps UDF ne peuvent pas être en ligne. Pour plus d’informations, consultez la configuration requise pour l’incorporation des fonctions UDF Scalar.

Vérifier si une fonction UDF scalaire peut être insérée

L’affichage sys.sql_modules catalogue inclut la colonne is_inlineable, qui indique si une fonction UDF est inlineable. La is_inlineable propriété provient de la vérification de la syntaxe à l’intérieur de la définition de la UDF. Le UDF scalaire n’est pas inliné avant le moment de la compilation.

La inline_eligibility_mask propriété explique quel type d’inlining s’applique à un UDF.

• Une valeur de 0 signifie que l’UDF n’est pas inligneable.
• Une valeur de 1 indique que l’UDF est éligible à l’inlining scalaire de la UDF.
• Une valeur de 2 signifie que l’UDF est éligible à l’inlining via un bloc d’expression.
• Une valeur de 3 signifie que l’UDF est éligible à l’une ou l’autre technique d’inlining.

Si une UDF scalaire est inligneable, cela ne garantit pas qu’elle soit toujours en ligne lors de la compilation de la requête.

Fabric Data Warehouse décide (par requête) quelle technique d’inlining appliquer.

Utilisez l’exemple de requête suivant pour vérifier si une fonction UDF scalaire est inlineable :

SELECT 
SCHEMA_NAME(b.schema_id) as function_schema_name,
    b.name as function_name,
       b.type_desc as function_type,
       a.is_inlineable
FROM sys.sql_modules AS a
     INNER JOIN sys.objects AS b
         ON a.object_id = b.object_id
WHERE b.type IN ('FN');

Si une fonction scalaire n’est pas inligneable dans sys.sql_modules.is_inlineable, vous pouvez toujours exécuter la requête comme un appel autonome, par exemple, pour définir une variable. Mais la fonction scalaire ne peut pas faire partie d’une SELECT ... FROM requête sur une table utilisateur. Par exemple :

CREATE FUNCTION [dbo].[custom_SYSUTCDATETIME]()
  RETURNS datetime2(6)
  AS
  BEGIN
   RETURN SYSUTCDATETIME();
  END

La fonction scalaire définie par l’utilisateur de l’échantillon dbo.custom_SYSUTCDATETIME n’est pas inlinéable en raison de l’utilisation d’une fonction système non déterminante, SYSUTCDATETIME(). Il échoue lorsqu’il est utilisé dans une SELECT ... FROM requête sur une table utilisateur, mais réussit en tant qu’appel autonome. Par exemple :

DECLARE @utcdate datetime2(7);
SET @utcdate = dbo.custom_SYSUTCDATETIME();
SELECT @utcdate as 'utc_date';

Exemples

R : Créer une fonction table incluse

L’exemple suivant crée une fonction en ligne à valeurs de table qui renvoie les informations clés sur les modules, en filtrant par paramètre objectType . Il inclut une valeur par défaut pour retourner tous les modules lorsque vous appelez la fonction avec le DEFAULT paramètre. Cet exemple utilise certaines des vues de catalogue système mentionnées dans les métadonnées.

CREATE FUNCTION dbo.ModulesByType (@objectType CHAR(2) = '%%')
RETURNS TABLE
AS
RETURN (
        SELECT sm.object_id AS 'Object Id',
            o.create_date AS 'Date Created',
            OBJECT_NAME(sm.object_id) AS 'Name',
            o.type AS 'Type',
            o.type_desc AS 'Type Description',
            sm.DEFINITION AS 'Module Description',
            sm.is_inlineable AS 'Inlineable'
        FROM sys.sql_modules AS sm
        INNER JOIN sys.objects AS o ON sm.object_id = o.object_id
        WHERE o.type LIKE '%' + @objectType + '%'
        );
GO

Appelez la fonction pour retourner toutes les fonctions en ligne à valeurs de table (IF) :

SELECT * FROM dbo.ModulesByType('IF'); -- SQL_INLINE_TABLE_VALUED_FUNCTION

Ou recherchez toutes les fonctions scalaires (FN) :

SELECT * FROM dbo.ModulesByType('FN'); -- SQL_SCALAR_FUNCTION

B. Combiner les résultats d’une fonction table inline

Cet exemple simple utilise le TVF en ligne créé précédemment pour démontrer comment combiner ses résultats avec d’autres tables en utilisant CROSS APPLY. Ici, vous sélectionnez toutes les colonnes des deux sys.objects et des résultats de ModulesByType pour toutes les lignes correspondantes à la type colonne. Pour plus d’informations sur l’utilisation APPLYde , voir la clause FROM plus JOIN, APPLY, PIVOT (Transact-SQL).

SELECT * 
FROM sys.objects AS o
CROSS APPLY dbo.ModulesByType(o.type);
GO

Chapitre C. Créer une fonction UDF scalaire

L’exemple suivant crée une fonction UDF scalaire inline qui masque un texte d’entrée.

CREATE OR ALTER FUNCTION [dbo].[cleanInput] (@InputString VARCHAR(100))
    RETURNS VARCHAR(50)
    AS
    BEGIN
        DECLARE @Result VARCHAR(50)
        DECLARE @CleanedInput VARCHAR(50)

        -- Trim whitespace
        SET @CleanedInput = LTRIM(RTRIM(@InputString))

        -- Handle empty or null input
        IF @CleanedInput = '' OR @CleanedInput IS NULL
        BEGIN
            SET @Result = ''
        END
        ELSE IF LEN(@CleanedInput) <= 2
        BEGIN
            -- If string length is 1 or 2, just return the cleaned string
            SET @Result = @CleanedInput
        END
        ELSE
        BEGIN
            -- Construct the masked string
            SET @Result = 
                LEFT(@CleanedInput, 1) +
                REPLICATE('*', LEN(@CleanedInput) - 2) +
                RIGHT(@CleanedInput, 1)
        END

        RETURN @Result
    END

Vous pouvez appeler la fonction comme suit :

DECLARE @input varchar(100) = '123456789'

SELECT dbo.cleanInput (@input) AS function_output;

Autres exemples de la façon dont vous pouvez utiliser des fonctions définies par l’utilisateur scalaire dans Fabric Data Warehouse :

Dans une SELECT instruction :

SELECT TOP 10 
t.id, t.name, 
dbo.cleanInput (t.name) AS function_output
FROM dbo.MyTable AS t;

Dans une WHERE clause :

 SELECT t.id, t.name, dbo.cleanInput(t.name) AS function_output
FROM dbo.MyTable AS t
WHERE dbo.cleanInput(t.name)='myvalue'

Dans une JOIN clause :

SELECT t1.id, t1.name, 
     dbo.cleanInput (t1.name) AS function_output, 
     dbo.cleanInput (t2.name) AS function_output_2
FROM dbo.MyTable1 AS t1
    INNER JOIN dbo.MyTable2 AS t2 
        ON dbo.cleanInput(t1.name)=dbo.cleanInput(t2.name);

Dans une ORDER BY clause :

SELECT  t.id, t.name, dbo.cleanInput (t.name) AS function_output
FROM dbo.MyTable AS t
ORDER BY function_output;

Dans les instructions DML (Data Manipulation Language) telles que INSERT, UPDATEou DELETE:

SELECT t.id, t.name, dbo.cleanInput (t.name) AS function_output 
INTO dbo.MyTable_new
FROM dbo.MyTable AS t;

UPDATE t
SET t.mycolumn_new = dbo.cleanInput (t.name)
FROM dbo.MyTable AS t;

DELETE t
FROM dbo.MyTable AS t
WHERE dbo.cleanInput (t.name) ='myvalue';

Contenu connexe

• Inlining UDF scalaire
• Assistant Migration de l’entrepôt de données Fabric

S’applique à: Azure Synapse Analytics Analytics Platform System (PDW)

Crée une fonction définie par l’utilisateur (UDF) dans Azure Synapse Analytics ou Analytics Platform System (PDW). Une fonction définie par l’utilisateur est une routine Transact-SQL qui accepte des paramètres, exécute une action, par exemple un calcul complexe, et retourne le résultat de cette action sous forme de valeur. Les fonctions table définies par l’utilisateur (TVF) retournent un type de données table.

• Dans Analytics Platform System (PDW), la valeur de retour doit être une valeur scalaire (unique).

• Dans Azure Synapse Analytics, CREATE FUNCTION peut retourner une table à l’aide de la syntaxe des fonctions table inline (préversion) ou renvoyer une valeur unique à l’aide de la syntaxe des fonctions scalaires.

• Dans les pools SQL serverless dans Azure Synapse Analytics, CREATE FUNCTION vous pouvez créer des fonctions table inline, mais pas des fonctions scalaires.

  Utilisez cette phrase pour créer une routine réutilisable que vous pourrez utiliser de la manière suivante :

• Dans les instructions Transact-SQL telles que SELECT

• Dans les applications qui appellent la fonction

• dans la définition d'une autre fonction définie par l'utilisateur ;

• pour définir une contrainte CHECK sur une colonne ;

• pour remplacer une procédure stockée.

• Utiliser une fonction inline comme prédicat de filtre pour une stratégie de sécurité

Conventions de la syntaxe Transact-SQL

Syntaxe

Syntaxe de la fonction scalaire

-- Transact-SQL Scalar Function Syntax (in dedicated pools in Azure Synapse Analytics and Parallel Data Warehouse)
-- Not available in the serverless SQL pools in Azure Synapse Analytics

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

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

Syntaxe de la fonction table inline

-- Transact-SQL Inline Table-Valued Function Syntax
-- Preview in dedicated SQL pools in Azure Synapse Analytics
-- Available in the serverless SQL pools in Azure Synapse Analytics
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] parameter_data_type
    [ = default ] }
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH SCHEMABINDING ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Les 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 suivre les règles des identifiants et être uniques au sein de la base de données et de son schéma.

@ parameter_name

Paramètre de la fonction définie par l’utilisateur. Vous pouvez déclarer un ou plusieurs paramètres.

Une fonction peut avoir jusqu’à 2 100 paramètres. Lorsqu’un utilisateur ou une application appelle une fonction, la valeur de chaque paramètre déclaré doit être fournie, sauf si un paramètre par défaut est défini.

Spécifiez un nom de paramètre en utilisant un signe @ comme premier caractère. Le nom du paramètre doit suivre les règles des identifiants. Les paramètres sont locaux à la fonction ; Vous pouvez utiliser les mêmes noms de paramètres dans d’autres fonctions. Les paramètres ne peuvent remplacer que les constantes ; ils ne peuvent pas être utilisés à la place des noms de tables, des noms de colonnes ou d’autres objets de base de données.

parameter_data_type

Type de données de paramètre. Pour les fonctions Transact-SQL, tous les types de données scalaires pris en charge dans Azure Synapse Analytics sont autorisés. Le type de données timedata (rowversion) n’est pas un type pris en charge.

[ = par défaut ]

Valeur par défaut du paramètre. Si vous définissez une valeur par défaut , vous pouvez exécuter la fonction sans spécifier une valeur pour ce paramètre.

Lorsqu’un paramètre de la fonction a une valeur par défaut, vous devez spécifier le mot-clé DEFAULT lors de l’appel de la fonction pour 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. Pour les fonctions Transact-SQL, tous les types de données scalaires pris en charge dans Azure Synapse Analytics sont autorisés. Le/ typede données timestamp rowversion n’est pas un type pris en charge. Les types non scalaires de curseur et de table ne sont pas autorisés.

function_body

Série d’instructions Transact-SQL. Le function_body ne peut pas contenir une SELECT instruction ni référencer les données de la base de données. Le function_body ne peut pas consulter les tables ou les vues. Le corps des fonctions peut appeler d’autres fonctions déterministes mais ne peut pas appeler des fonctions non déterministes.

Dans les fonctions scalaires, function_body est une série d’instructions Transact-SQL qui, ensemble, prennent une valeur scalaire.

scalar_expression

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

select_stmt

Instruction unique SELECT qui définit la valeur de retour d’une fonction table inline. Pour une fonction à valeurs de table en ligne, il n’existe pas de corps de fonction ; Le tableau est l’ensemble des résultats d’une seule SELECT affirmation.

TABLE

Indique que la valeur de retour de la fonction table est une table. Vous ne pouvez passer que des constantes et @local_variables aux TVFs.

Dans les TVF en ligne (aperçu), vous définissez la TABLE valeur de retour à travers une seule SELECT instruction. Les fonctions en ligne n’ont pas de variables de retour associées.

<function_option>

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

SCHEMABINDING

Indique que la fonction est liée aux objets de base de données auxquels elle fait référence. Lorsque vous spécifiez SCHEMABINDING, vous ne pouvez pas modifier les objets sous-jacents (comme une vue ou une table, par exemple) d’une manière qui affecte la définition de la fonction. Vous devez d’abord modifier ou supprimer la définition de la fonction pour supprimer les dépendances sur l’objet que vous souhaitez modifier.

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

• Tu laisses tomber la fonction.

• Vous ALTER utilisez la fonction et supprimez l’option SCHEMABINDING .

Vous ne pouvez lier un schéma à une fonction que si les conditions suivantes sont vraies :

• Toutes les fonctions définies par l’utilisateur auxquelles la fonction fait référence sont également liées au schéma.

• Les références de fonction utilisent des noms à une ou deux parties.

• Dans l’ensemble des UDF, vous ne pouvez référencer que les fonctions intégrées et d’autres UDF dans la même base de données.

• L’utilisateur qui exécute l’instruction CREATE FUNCTION dispose de la permission REFERENCES sur les objets de base de données auxquels la fonction fait référence.

Pour supprimer SCHEMABINDING, utilisez ALTER.

RENVOIE NULL SUR L’ENTRÉE NULL | APPELÉ SUR L’ENTRÉE NULL

Spécifie l’attribut OnNULLCall d’une fonction scalaire. Si vous ne spécifiez pas cet attribut, CALLED ON NULL INPUT il est implicite par défaut, et le corps de la fonction s’exécute même si NULL il est passé comme un argument.

Bonnes pratiques

Si vous ne créez pas de fonction définie par l’utilisateur avec la clause SCHEMABINE, les modifications des objets sous-jacents peuvent affecter la définition de la fonction et provoquer des résultats inattendus lors de l’invocation. Spécifiez la WITH SCHEMABINDING clause lors de la création de la fonction. Cette clause garantit que vous ne pouvez pas modifier les objets référencés dans la définition de la fonction à moins de modifier également la fonction.

Interopérabilité

Les instructions suivantes sont valides dans une fonction scalaire :

• Instructions d'affectation

• Des instructions de contrôle du flux, sauf TRY... Déclarations CATCH.

• DECLARE qui définissent des variables de données locales.

Dans une fonction à valeurs de table en ligne (aperçu), vous ne pouvez utiliser qu’une seule instruction select.

Limites

Vous ne pouvez pas utiliser des fonctions définies par l’utilisateur pour effectuer des actions qui modifient l’état de la base de données.

Vous pouvez imbriquer des fonctions définies par l’utilisateur. Une fonction définie par l’utilisateur peut en appeler une autre. Le niveau de nesting augmente lorsque la fonction appelée commence son exécution, et diminue lorsque la fonction appelée termine son exécution. Si vous dépassez les niveaux maximaux de nesting, toute la chaîne des fonctions appelantes échoue.

Vous ne pouvez pas créer d'objets, y compris des fonctions, dans la master base de données de votre pool SQL serverless dans Azure Synapse Analytics.

Métadonnées

Cette section répertorie les vues de catalogue système que vous pouvez utiliser pour retourner des métadonnées sur les fonctions définies par l’utilisateur.

• sys.sql_modules : affiche la définition de Transact-SQL fonctions définies par l’utilisateur. Par exemple :

  SELECT definition, type   
  FROM sys.sql_modules AS m  
  JOIN sys.objects AS o   
      ON m.object_id = o.object_id   
      AND type = ('FN');
  

• sys.parameters : affiche des informations sur les paramètres définis dans les fonctions définies par l’utilisateur.

• sys.sql_expression_dependencies : affiche les objets sous-jacents référencés par une fonction.

Autorisations

Nécessite CREATE FUNCTION une autorisation dans la base de données et une autorisation ALTER sur le schéma dans lequel la fonction est créée.

Exemples

R : Utiliser une fonction scalaire définie par l’utilisateur pour modifier un type de données

Cette fonction simple prend un type de données int en entrée, et renvoie un type de données décimal(10,2) en sortie.

CREATE FUNCTION dbo.ConvertInput (@MyValueIn int)  
RETURNS decimal(10,2)  
AS  
BEGIN
    DECLARE @MyValueOut int;  
    SET @MyValueOut= CAST( @MyValueIn AS decimal(10,2));  
    RETURN(@MyValueOut);  
END;  
GO  

SELECT dbo.ConvertInput(15) AS 'ConvertedValue';  

B. Créer une fonction table incluse

L’exemple suivant crée une fonction en ligne à valeurs de table qui renvoie les informations clés sur les modules, en filtrant par paramètre objectType . Il inclut une valeur par défaut pour retourner tous les modules lorsque vous appelez la fonction avec le DEFAULT paramètre. Cet exemple utilise certaines des vues de catalogue système mentionnées dans les métadonnées.

CREATE FUNCTION dbo.ModulesByType(@objectType CHAR(2) = '%%')
RETURNS TABLE
AS
RETURN
(
    SELECT 
        sm.object_id AS 'Object Id',
        o.create_date AS 'Date Created',
        OBJECT_NAME(sm.object_id) AS 'Name',
        o.type AS 'Type',
        o.type_desc AS 'Type Description', 
        sm.definition AS 'Module Description'
    FROM sys.sql_modules AS sm  
    JOIN sys.objects AS o ON sm.object_id = o.object_id
    WHERE o.type like '%' + @objectType + '%'
);
GO

Vous pouvez appeler la fonction pour retourner tous les objets de la vue (V) avec :

select * from dbo.ModulesByType('V');

Chapitre C. Combiner les résultats d’une fonction table inline

Cet exemple simple utilise le TVF en ligne créé précédemment pour démontrer comment combiner ses résultats avec d’autres tables en utilisant CROSS APPLY. Dans cet exemple, vous sélectionnez toutes les colonnes des deux sys.objects et des résultats de ModulesByType pour toutes les lignes correspondantes sur la type colonne. Pour plus d’informations sur l’utilisation APPLYde , voir la clause FROM plus JOIN, APPLY, PIVOT (Transact-SQL).

SELECT * 
FROM sys.objects o
CROSS APPLY dbo.ModulesByType(o.type);
GO

Étape suivante