Partager via

CRÉER FONCTION

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

CREATE FUNCTION peut créer des fonctions table inline et des fonctions scalaires.

Notes

Les fonctions définies par l’utilisateur scalaire sont une fonctionnalité en préversion dans Fabric Data Warehouse.

Important

Dans Fabric Data Warehouse, les fonctions définies par l’utilisateur scalaire doivent être inline pour être utilisées avec SELECT ... FROM des requêtes sur des tables utilisateur, mais vous pouvez toujours créer des fonctions qui ne sont pas inline. Les fonctions définies par l’utilisateur scalaire qui ne sont pas inlineables fonctionnent dans un nombre limité de scénarios. Vous pouvez vérifier si une fonction UDF peut être insérée.

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 scalaires retournent une valeur scalaire, telle qu’un nombre ou une chaîne. Les fonctions table définies par l’utilisateur retournent une table.

Permet CREATE FUNCTION de créer une routine T-SQL réutilisable qui peut être utilisée de ces manières :

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

Conseil / Astuce

Vous pouvez spécifier CREATE OR ALTER FUNCTION de créer une fonction si elle n’existe pas par ce nom, ou modifier une fonction existante, dans une seule instruction.

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 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 de 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 utilisant un signe @ comme premier caractère. Le nom du paramètre doit respecter 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 respecté lorsque vous passez des paramètres dans une procédure stockée, une fonction définie par l’utilisateur ou lorsque vous déclarez et définissez des variables dans une instruction batch. Par exemple, si une variable est définie comme char(3) , puis réglée sur une valeur supérieure à trois caractères, les données sont tronquées à la taille définie et l’instruction INSERT ou UPDATE réussit.

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 une valeur default est définie, la fonction peut être exécutée sans spécifier de valeur pour ce paramètre.

Lorsqu’un paramètre de la fonction a une valeur par défaut, le mot-clé DEFAULT doit être spécifié 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’horodatagerowversion/. Les types noncalaires comme la table 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

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 table inline, il n’existe aucun corps de fonction ; la table est le jeu de résultats d’une instruction unique SELECT .

TABLEAU

Indique que la valeur de retour de la fonction table est une table. Seules des constantes et des @local_variables peuvent être passés aux fonctions table.

Dans les fichiers TVFs inline (préversion), la valeur de retour TABLE est définie par le biais d’une instruction unique SELECT . Aucune variable retournée n'est associée à une fonction en ligne.

<function_option>

Dans Fabric Data Warehouse, les mots clés et EXECUTE AS les INLINEENCRYPTIONmots clés ne sont pas pris en charge.

Les options de fonction prises en charge sont les suivantes :

SCHEMABINDING

Indique que la fonction est liée aux objets de base de données auxquels elle fait référence. Si SCHEMABINDING est précisé, les objets de base ne peuvent pas être modifiés d'une manière susceptible d'affecter la définition de la fonction. Cette dernière doit d'ailleurs être modifiée ou supprimée au préalable pour supprimer les dépendances par rapport à l'objet qui doit être modifié.

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 :

  • Toute fonction définie par l’utilisateur référencée par la fonction est également liée au schéma.

  • Les objets référencés par la fonction sont référencés à l’aide d’un nom en deux parties.

  • Seules les fonctions intégrées et les autres fonctions définies par l’utilisateur dans la même base de données peuvent être référencées dans le corps des fonctions définies par l’utilisateur.

  • L’utilisateur qui a exécuté l’instruction CREATE FUNCTION dispose de l’autorisation REFERENCES sur les objets de base de données référencés par la fonction.

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. S’il n’est pas spécifié, CALLED ON NULL INPUT est implicite par défaut et le corps de la fonction s’exécute même s’il NULL est passé en tant qu’argument.

Bonnes pratiques

  • Si une fonction définie par l’utilisateur n’est pas créée avec la liaison de schéma, les modifications apportées aux objets sous-jacents peuvent affecter la définition de la fonction et produire des résultats inattendus lorsqu’elle est appelée. Il est recommandé de spécifier la WITH SCHEMABINDING clause lorsque vous créez 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.

  • Écriture de vos fonctions définies par l’utilisateur pour être inlineables. Pour plus d’informations, consultez Incorporation des fonctions UDF scalaires.

Interopérabilité

Fonctions définies par l’utilisateur inline

Dans une fonction table incluse, une seule instruction select est autorisée.

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 :

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

  • Les fonctions définies par l’utilisateur scalaire ne peuvent pas être utilisées dans une requête lorsque :

    • UDF est directement appelé dans une GROUP BY clause.
    • La fonction UDF est directement appelée dans une ORDER BY clause.
    • la requête appelante a une expression de table commune (CTE).

  • Les fonctions définies par l’utilisateur scalaire récursive ne sont pas prises en charge.

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

  • 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

Notes

Pendant la préversion actuelle, les limitations sont susceptibles de changer.

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 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 dans Fabric Data Warehouse peuvent être imbriquées jusqu’à quatre niveaux lorsqu’un corps UDF fait référence à une fonction table/vue/table en ligne, ou jusqu’à 32 niveaux dans le cas contraire. Le dépassement des niveaux maximal d’imbrication entraîne l’échec de la chaîne de fonctions appelantes.

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

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 l’incorporation de fonctions UDF scalaires pour compiler et exécuter du code défini par l’utilisateur de manière distribuée. L’inlining UDF scalaire est activée par défaut.

Bien que l’incorporation UDF scalaire soit une technique d’optimisation des performances introduite en premier dans Microsoft SQL Server 2019 (15.0), dans Fabric Data Warehouse, elle détermine l’ensemble de scénarios pris en charge. Dans Fabric Data Warehouse, les fonctions définies par l’utilisateur scalaire sont automatiquement transformées en expressions scalaires ou sous-requêtes scalaires qui sont remplacées dans la requête appelante à la place de l’opérateur UDF.

Certaines syntaxes T-SQL rendent non linéaire une fonction UDF scalaire. Les fonctions qui contiennent une WHILE boucle, plusieurs RETURN instructions ou un appel à une fonction intégrée SQL non déterministe (par exemple GETUTCDATE() ou GETDATE()) ne peuvent pas être inline. 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é est dérivée de la vérification de la syntaxe à l’intérieur de la définition UDF. La fonction UDF scalaire n’est pas insérée avant l’heure de compilation. Une valeur indiquant 1 que l’UDF est inlineable, tandis qu’une valeur indiquant 0 qu’elle n’est pas inlineable. Si une fonction UDF scalaire est inlineable, elle ne garantit pas qu’elle sera toujours inline lorsque la requête est compilée.

Fabric Data Warehouse décide (par requête) s’il faut inliner une fonction UDF, en fonction de la complexité globale des requêtes.

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 inline sys.sql_modules.is_inlineabledans , vous pouvez toujours exécuter la requête en tant qu’appel autonome, par exemple, pour définir une variable. Toutefois, 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

L’exemple dbo.custom_SYSUTCDATETIME de fonction scalaire définie par l’utilisateur n’est pas inline en raison de l’utilisation d’une fonction système non déterminable. SYSUTCDATETIME() Elle échoue lorsqu’elle est utilisée dans une requête sur une SELECT ... FROM 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 table inline pour retourner des informations clés sur les modules, en filtrant selon le paramètre objectType . Elle inclut une valeur par défaut pour retourner tous les modules lorsque la fonction est appelée avec le DEFAULT paramètre. Cet exemple utilise certains des affichages catalogue système mentionnés dans 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

La fonction peut ensuite être appelée pour retourner toutes les fonctions table inline (IF) avec :

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 la fonction table inline qui a été créée pour montrer comment ses résultats peuvent être combinés avec d’autres tables à l’aide de CROSS APPLY. Ici, nous sélectionnons toutes les colonnes à partir des deux sys.objects et des résultats de ModulesByType toutes les lignes correspondantes sur la type colonne. Pour plus d’informations sur l’utilisation de l’application, consultez 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

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 instruction pour créer une routine réutilisable, exploitable :

  • Dans les instructions Transact-SQL telles que SELECT

  • dans des applications appelant 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é

Conseil / Astuce

Pour obtenir la syntaxe dans Fabric Data Warehouse, consultez la version de CREATE FUNCTION pour Microsoft Fabric Data Warehouse.

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 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 de 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 utilisant un signe @ comme premier caractère. Le nom du paramètre doit respecter 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 respecté lorsque vous passez des paramètres dans une procédure stockée, une fonction définie par l’utilisateur ou lorsque vous déclarez et définissez des variables dans une instruction batch. Par exemple, si une variable est définie comme char(3) , puis réglée sur une valeur supérieure à trois caractères, les données sont tronquées à la taille définie et l’instruction INSERT ou UPDATE réussit.

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 timestamp (rowversion) n’est pas un type pris en charge.

[ = par défaut ]

Valeur par défaut du 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.

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. 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 timestamp rowversion/n’est pas un type pris en charge. Les types non scalaires cursor et table ne sont pas autorisés.

function_body

Série d’instructions Transact-SQL. Le function_body ne peut pas contenir d’instruction SELECT et ne peut pas référencer les données de base de données. Le function_body ne peut pas référencer des tables ou des vues. Le corps de la fonction peut appeler d’autres fonctions déterministes, mais ne peut pas appeler de 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 table inline, il n’existe aucun corps de fonction ; la table est le jeu de résultats d’une instruction unique SELECT .

TABLEAU

Indique que la valeur de retour de la fonction table est une table. Seules des constantes et des @local_variables peuvent être passés aux fonctions table.

Dans les fichiers TVFs inline (préversion), la valeur de retour TABLE est définie par le biais d’une instruction unique SELECT . Aucune variable retournée n'est associée à une fonction en ligne.

<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. Si SCHEMABINDING est précisé, les objets de base ne peuvent pas être modifiés d'une manière susceptible d'affecter la définition de la fonction. Cette dernière doit d'ailleurs être modifiée ou supprimée au préalable pour supprimer les dépendances par rapport à l'objet qui doit être modifié.

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 :

  • Toute fonction définie par l’utilisateur référencée par la fonction est également liée au schéma.

  • Les fonctions et autres fonctions définies par l’utilisateur référencées par la fonction sont référencées à l’aide d’un nom en une ou deux parties.

  • Seules les fonctions intégrées et les autres fonctions définies par l’utilisateur dans la même base de données peuvent être référencées dans le corps des fonctions définies par l’utilisateur.

  • L’utilisateur qui a exécuté l’instruction CREATE FUNCTION dispose de l’autorisation REFERENCES sur les objets de base de données référencés par la fonction.

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. S’il n’est pas spécifié, CALLED ON NULL INPUT est implicite par défaut et le corps de la fonction s’exécute même s’il NULL est passé en tant qu’argument.

Bonnes 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. Il est recommandé de spécifier la WITH SCHEMABINDING clause lorsque vous créez 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.

Interopérabilité

Les instructions suivantes sont valides dans une fonction scalaire :

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

Dans une fonction table inline (préversion), une seule instruction SELECT est autorisée.

Limites

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

Les fonctions définies par l'utilisateur 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. Le dépassement des niveaux d'imbrication maximum autorisés, provoque l'échec de la totalité de la chaîne de fonctions appelantes. Dans Microsoft Fabric Data Warehouse, les fonctions définies par l’utilisateur peuvent être imbriquées jusqu’à cinq niveaux.

Les objets, y compris les fonctions, ne peuvent pas être créés dans la base de données master 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 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.

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 comme entrée et retourne un type de données decimal(10,2) comme 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';

Notes

Les fonctions scalaires ne sont pas disponibles dans les pools SQL serverless.

B. Créer une fonction table incluse

L’exemple suivant crée une fonction table inline pour retourner des informations clés sur les modules, en filtrant selon le paramètre objectType . Elle inclut une valeur par défaut pour retourner tous les modules lorsque la fonction est appelée avec le DEFAULT paramètre. Cet exemple utilise certains des affichages catalogue système mentionnés dans 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

La fonction peut ensuite être appelée pour renvoyer tous les objets d’affichage (V) avec :

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

Notes

Les fonctions table inline sont disponibles dans les pools SQL sans serveur, mais en préversion dans les pools SQL dédiés.

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

Cet exemple simple utilise la fonction table inline qui a été créée pour montrer comment ses résultats peuvent être combinés avec d’autres tables à l’aide de CROSS APPLY. Ici, nous sélectionnons toutes les colonnes à partir des deux sys.objects et des résultats de ModulesByType toutes les lignes correspondantes sur la type colonne. Pour plus d’informations sur l’utilisation de l’application, consultez la clause FROM plus JOIN, APPLY, PIVOT (Transact-SQL).

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

Notes

Les fonctions table inline sont disponibles dans les pools SQL sans serveur, mais en préversion dans les pools SQL dédiés.

Étape suivante

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