Fonctions table définies par l'utilisateur

Les fonctions définies par l'utilisateur qui retournent un type de données table peuvent efficacement se substituer aux vues. Ces fonctions sont appelées fonctions table. Une fonction table définie par l'utilisateur peut être utilisée dans les requêtes Transact-SQL, aux mêmes emplacements que les expressions de table ou de vue. Alors que les vues sont limitées à une seule instruction SELECT, les fonctions définies par l'utilisateur peuvent contenir davantage d'instructions, ce qui permet d'élaborer une logique plus efficace que dans les vues.

Une fonction table définie par l'utilisateur peut également remplacer les procédures stockées qui retournent un seul jeu de résultats. La table retournée par une fonction définie par l'utilisateur peut être référencée dans la clause FROM d'une instruction Transact-SQL ; cette fonctionnalité est interdite aux procédures stockées qui retournent des jeux de résultats.

Composants d'une fonction table définie par l'utilisateur

Dans une fonction table définie par l'utilisateur :

• La clause RETURNS définit un nom de variable de retour locale pour la table retournée par la fonction, ainsi que le format de la table. Le nom de la variable de retour locale est de portée locale au sein de la fonction.
• Les instructions Transact-SQL contenues dans le corps de la fonction créent et insèrent des lignes dans la variable de retour définie par la clause RETURNS.
• Lorsqu'une instruction RETURN est exécutée, les lignes insérées dans la variable sont retournées en guise de sortie tabulaire de la fonction. L'instruction RETURN ne peut pas contenir d'argument.

Aucune instruction Transact-SQL d'une fonction table ne peut retourner un jeu de résultats directement à un utilisateur. Les informations que la fonction peut retourner à l'utilisateur se limitent à la table.

Remarque :

L'option de table text in row prend automatiquement la valeur 256 dans le cas d'une table retournée par une fonction définie par l'utilisateur. Cette valeur ne peut pas être modifiée. Les instructions READTEXT, WRITETEXT et UPDATETEXT ne peuvent pas être utilisées pour lire ou écrire des parties de colonnes de type text, ntext ou image dans la table. Pour plus d'informations, consultez Données dans la ligne.

Exemple

L'exemple suivant crée la fonction dbo.ufnGetContactInformation et présente les composants de la fonction table. Dans cette fonction, le nom de la variable de retour locale est @retContactInformation. Les instructions contenues dans le corps de la fonction insèrent des lignes dans cette variable pour élaborer le résultat de table retourné par la fonction.

USE AdventureWorks;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
    DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE 
(
    -- Columns returned by the function
    ContactID int PRIMARY KEY NOT NULL, 
    FirstName nvarchar(50) NULL, 
    LastName nvarchar(50) NULL, 
    JobTitle nvarchar(50) NULL, 
    ContactType nvarchar(50) NULL
)
AS 
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
    DECLARE 
        @FirstName nvarchar(50), 
        @LastName nvarchar(50), 
        @JobTitle nvarchar(50), 
        @ContactType nvarchar(50);
    -- Get common contact information
    SELECT 
        @ContactID = ContactID, 
        @FirstName = FirstName, 
        @LastName = LastName
    FROM Person.Contact 
    WHERE ContactID = @ContactID;
    SELECT @JobTitle = 
        CASE 
            -- Check for employee
            WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e 
                WHERE e.ContactID = @ContactID) 
                THEN (SELECT Title 
                    FROM HumanResources.Employee 
                    WHERE ContactID = @ContactID)
            -- Check for vendor
            WHEN EXISTS(SELECT * FROM Purchasing.VendorContact AS vc 
                    INNER JOIN Person.ContactType AS ct 
                    ON vc.ContactTypeID = ct.ContactTypeID 
                WHERE vc.ContactID = @ContactID) 
                THEN (SELECT ct.Name 
                    FROM Purchasing.VendorContact AS vc 
                        INNER JOIN Person.ContactType AS ct 
                        ON vc.ContactTypeID = ct.ContactTypeID 
                    WHERE vc.ContactID = @ContactID)
            -- Check for store
            WHEN EXISTS(SELECT * FROM Sales.StoreContact AS sc 
                    INNER JOIN Person.ContactType AS ct 
                    ON sc.ContactTypeID = ct.ContactTypeID 
                WHERE sc.ContactID = @ContactID) 
                THEN (SELECT ct.Name 
                    FROM Sales.StoreContact AS sc 
                        INNER JOIN Person.ContactType AS ct 
                        ON sc.ContactTypeID = ct.ContactTypeID 
                    WHERE ContactID = @ContactID)
            ELSE NULL 
        END;
    SET @ContactType = 
        CASE 
            -- Check for employee
            WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e 
                WHERE e.ContactID = @ContactID) 
                THEN 'Employee'
            -- Check for vendor
            WHEN EXISTS(SELECT * FROM Purchasing.VendorContact AS vc 
                    INNER JOIN Person.ContactType AS ct 
                    ON vc.ContactTypeID = ct.ContactTypeID 
                WHERE vc.ContactID = @ContactID) 
                THEN 'Vendor Contact'
            -- Check for store
            WHEN EXISTS(SELECT * FROM Sales.StoreContact AS sc 
                    INNER JOIN Person.ContactType AS ct 
                    ON sc.ContactTypeID = ct.ContactTypeID 
                WHERE sc.ContactID = @ContactID) 
                THEN 'Store Contact'
            -- Check for individual consumer
            WHEN EXISTS(SELECT * FROM Sales.Individual AS i 
                WHERE i.ContactID = @ContactID) 
                THEN 'Consumer'
        END;
    -- Return the information to the caller
    IF @ContactID IS NOT NULL 
    BEGIN
        INSERT @retContactInformation
        SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
    END;
    RETURN;
END;
GO

L'exemple suivant utilise la fonction table dbo.ufnGetContactInformation dans la clause FROM de deux instructions SELECT.

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

Voir aussi

Concepts

Fonctions définies par l'utilisateur inline
Fonctions déterministes et non déterministes
Réécriture de procédures stockées en fonctions

Autres ressources

Création de fonctions définies par l'utilisateur

Aide et Informations

Assistance sur SQL Server 2005